Зачем мне знать SQL и Базы Данных, ведь у меня есть ORM?Денис РезникIntapp, Inc.

Agenda • .Net ORM History which I remember• Simple Queries• Column List• Lazy Load• Data Types• Data Modification• Complex Query• Parameter Sniffing

ORM History which I remember • ADO.NET• Data Sets (not too bad)• LLBL Gen, NHibernate (better than data sets)• LINQ to SQL (Awesome!)• Entity Framework should be even more awesome• Entity Framework is more powerful than L2S• Entity Framework Code First• Entity Framework is no so good, so what?

Simple Query

DEMO: Simple Query

Column List

DEMO: Column List

Lazy Load

Index Seek

…

…

1 .. 1M

1 .. 2K 2K+1 .. 4K 1M-2K .. 1M

1 .. 300 301 .. 800 801 .. 1,5K 1,5K+1 .. 2K

SELECT * FROM UsersWHERE Id = 523

Index Scan

…

…

1 .. 1M

1 .. 2K 2K+1 .. 4K 1M-2K .. 1M

1 .. 300 301 .. 800 801 .. 1,5K 1,5K+1 .. 2K

SELECT * FROM Users

Index Range Scan

…

…

1 .. 1M

1 .. 2K 2K+1 .. 4K 1M-2K .. 1M

1 .. 300 301 .. 800 801 .. 1,5K 1,5K+1 .. 2K

SELECT * FROM UsersWHERE Id BETWEEN 700 AND 1700

DEMO: Lazy Load

Data Types

DEMO: Data Types

Data Modification

DEMO: Data Modification

Complex Query

DEMO: Complex Query

Parameter Sniffing

Parameter Sniffing - Stored Procedure

EXEC ReportCheckSecurityRequest @UserId = 1

Query Processor

SQL Server Cache

Procedure cachePlan created and cached for the @UserId = 1

Procedure cache

EXEC ReportCheckSecurityRequest @UserId = 22

Query executes using the query plan created for @UserId = 1

Parameter Sniffing – Parametrized Query

SELECT * FROM Clients WHERE Id = @Id

Query Processor

SQL Server Cache

Procedure cachePlan created and cached for the @Id = 1

Procedure cacheQuery executes using the query plan created for @Id = 1

SELECT * FROM Clients WHERE Id = @Idsp_executesql N'SELECT * FROM Clients WHERE Id = @Id', N'@Id int', 1

sp_executesql N'SELECT * FROM Clients WHERE Id = @Id', N'@Id int', 22

Dynamic SQL – Multiple Plans

SELECT * FROM Clients WHERE Id = 1

Query Processor

SQL Server Cache

Procedure cacheNew query plan created and cached. Query executed using newly created plan.

Procedure cache

SELECT * FROM Clients WHERE Id = 22

New query plan again created and cached. Query executed using newly created plan.

SELECT * FROM Clients WHERE Id = 1

Query executed using the query plan, created for the first query.

Procedure cache

DEMO: Parameter Sniffing

Summary • ORM is really a nice thing• But it still requires strong SQL background• Database internals knowledge is a skill that very

nice to have even in the ORM world• Few Advices

• Sometimes check the generated SQL and its query plan• For complex queries consider raw SQL anyway• Consider using Dapper• Invest your time to learn database internals

Thank You!

@denisreznikdenisreznik@gmail.comhttp://reznik.uneta.com.ua/ https://www.facebook.com/denis.reznik.5https://www.linkedin.com/pub/denis-reznik/3/502/234