Entity Framework

ou comment réconcilier les développeurs et DBA sur l'accès aux données

Christian Robert Senior Database Consultant [email protected]

EvoluSys SA Matthieu Mezil Senior Developer Consultant [email protected]

Access It IDF

About Matthieu

Matthieu Mezil – [email protected]

Work on .NET Server since 1.0 (2002) C# MVP (2008 – 2009) INETA Speaker MCT Specialized on Entity Framework, C#, T4, Architecture.

My French blog : blogs.codes-sources.com/matthieu My English blog : msmvps.com/blogs/matthieu

Access It IdF in brief…

Consulting & training on Microsoft technologies only!

20+ consultants/experts, including 11 MVP 2 domains of expertise: Everything about .NET & Visual Studio!

MS Application Platform: SharePoint & SQL Server / BI

.NET

Framework et langages .Net, AZURE, Silverlight, WPF/Surface, VS/TFS, Windows Phone 7,…

Plateforme Applicative

SQL Server (SSIS, SQL, SSAS, SSRS), PowerPivot, SharePoint & on-line, Office 365,…

About Christian

Christian Robert [email protected]

Have worked with SQL Server since 7.0

Specialized on SQL Server Relational Engine, around Performance Server. Projects for : CICR, ricardo.ch, … My blog : blogs.codes-sources.com/christian Swiss SQL Server User Group : www.sqlpass.ch

EvoluSys… the data experts

Swiss company founded in 2006 Consulting Performance review Security audit of the database server Architecture choice DataWarehouse conception Reporting & Dashboard needs Highly skilled and certified trainers More info on : www.evolusys.org

Training

more info on : [email protected]

SQL Server 2008 & R2

Administration - 5 days • 6 to 10 June 2011 - Genève • Fr. 3190.00

SQL Server 2008 & R2

Development - 5 days • 16 to 20 May 2011 - Genève • Fr. 3190.00

SQL Server « Denali »

New Features - 5 days • September 2011 - Genève • Fr. 3990.00

SQL Server 2008 R2

Powerpivot

SQL Server

Business Intelligence • TBD - Genève or Lausanne • TBD - Genève or Lausanne

Introducing Entity Framework

DB relational conception != Entities object conception. EF runtime does the mapping job for you EF abstracts the DB schema and DB provider. Your code works with SQL Server, Oracle, etc LINQ To Entities simplifies the querying

Démo

EF advantages

EF advantages’ for developers

The mapping complexity is done by EF. You can very easily have your entities object conception without taking care of the DB constraints LINQ To Entities are better: Compiled => no repo mistake Intellisense Better readability Typed result Written on C# or VB.NET

EF advantages’ for developers (continued)

SQL written by developers is often not the best one SQL generated by EF is pretty good… when you understand what you do!

EF advantages’ for DBAs

Possibility to change the database without any impact on the application As all the queries are fully qualified it reduced a lot the risk to have broken queries on small schema changes When the mapping is outside of the application more changes are even possible Security SQL generated is safe and risk of code injection is very low

EF advantages’ for DBAs (continued)

Source code management LINQ queries are part of the application code and also part of the source controller Dependencies No risk of objects not found at execution time, dependencies are enforced at compile time Works for all SQL-based database servers Who provides an EF provider

What’s possible with EF ?

Horizontal and Vertical table Split Take a table, put 2 columns on a new table and 3 others columns in an other table Split the content of a table in 2 parts and map your Entity to one of them Convert a complex LINQ to EF query to a Stored Procedure Use of Stored Procedures for INSERT / UPDATE / DELETE operations

Démo

Not (yet) available with EF

Recursive CTEs PIVOT Ranking functions Support for CLR UDTs (spatial, hierarchyID) Temp tables Filestream Query hints (except in plan guides) FullText queries TVF (Table Value Function) BULK INSERT support

Troubleshooting EF on server side

Dynamic SQL considerations

EF is a SQL Generator so most of issues seen with that kind of tools remains Who is generating that code ?

Difficult to cluster queries by types Almost no possibilities to change the query content Side effects Security enforcement (not possible to trust code) Number of queries to consider can be huge

Performance considerations

The behavior of EF queries are the exact same as Stored Stored Procedures are compiled at first execution cache. On second execution the plan is reused from and provide parameters… The behavior is the same,

SQL Profiler is your friend

Allow you to intercept query : Text Reads / Writes CPU Execution Time Attention, when you copy and paste a query from SQL Profiler to SSMS the behavior change Management Studio use slightly different connection parameters, so the query will generate a new plan and new reads, writes and CPU time!

Usefull Dynamic Management Views

Querying the Plan cache with Management Views (DMVs): sys.dm_exec_cached_plans

sys.dm_exec_plan_attributes

sys.dm_exec_query_stats

Some Management Functions (DMFs): sys.dm_exec_sql_text

sys.dm_exec_query_plan

Plan cache

As describe before the behavior of EF vs SP are similar EF uses parametrized queries so the cache can be larger The cache entry is based on the query hash (exact text content including comments) Use the server option : optimize for ad hoc workloads’ The query plan is kept in cache only at 2 nd execution If using different users to connect to the server, some other cache can suffer of contention Really rare since SQL 2005 SP2

Plan guides

Now, you know your problematic queries… But it’s dynamic… no way to update them :( Since SQL Server 2005 Possibility to identify a pattern of queries… … and apply them Hints … and provides to the engine alternates plans (but engine is not obliged to use them) Plan guides are the ultimate’s solution to change the query’s behavior on the server !

Plan guide for Recompile

This query’s execution time change a lot from time to time Execution plan don’t change but parameters change When is queries is expected to have different behavior with different parameters it should be good to always recompile that query Create a plan guide for that query pattern Dynamically add ‘OPTION (RECOMPILE)’

Plan guide with OPTIMIZE FOR

This query is fast the morning, and suddenly become slow for a certain time Execution plan changes on statistics update and the next parameter provided is not always perfect for the compilation Remember that the statistics are recomputed every 20% of data changes on a column, so the recompilation can occur very frequently Create a plan guide to force the engine to compile the query based on a specific values or statistics of the columns Dynamically add ‘OPTION (OPTIMIZE FOR UNKNOWN)’ Dynamically add ‘OPTION (OPTIMIZE @p1 FOR N‘myvalue’)

Démo

Conclusion

Entity Framework makes developer job easier Entity Framework improves development flexibility Don’t forget that there is a database server behind your queries Watch your queries and plans Optimization is a not a one shot task… It’s a day to day work !

© 2011 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.

The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.