entity framework ou comment r concilier les d veloppeurs et dba sur l acc s aux donn es l.
Skip this Video
Loading SlideShow in 5 Seconds..
Entity Framework ou comment réconcilier les développeurs et DBA sur l'accès aux données PowerPoint Presentation
Download Presentation
Entity Framework ou comment réconcilier les développeurs et DBA sur l'accès aux données

Loading in 2 Seconds...

play fullscreen
1 / 31

Entity Framework ou comment réconcilier les développeurs et DBA sur l'accès aux données - PowerPoint PPT Presentation

  • Uploaded on

Entity Framework ou comment réconcilier les développeurs et DBA sur l'accès aux données. Matthieu Mezil. Christian Robert. Senior Developer Consultant mmezil@access-it.fr. Senior Database Consultant cro@evolusys.org. EvoluSys SA. Access It IDF. About Matthieu.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

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

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
entity framework ou comment r concilier les d veloppeurs et dba sur l acc s aux donn es

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

Matthieu Mezil

Christian Robert

Senior Developer Consultant


Senior Database Consultant


EvoluSys SA

Access It IDF

about matthieu
About Matthieu

Matthieu Mezil – mmezil@access-it.fr

Work on .NET Server since 1.0 (2002)

C# MVP (2008 – 2009)Data Platform Development MVP (since 2010)

INETA Speaker


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
Access It IdF in brief…
  • Consulting & training on Microsoft technologies only!
  • 20+ consultants/experts, including11 MVP
  • 2 domains of expertise:
    • Everythingabout .NET & Visual Studio!
    • MS Application Platform: SharePoint & SQL Server / BI


Plateforme Applicative

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

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

about christian
About Christian

Christian Robert - cro@evolusys.org

Have worked with SQL Server since 7.0

SQL Server MVP since 2007Microsoft Certified Master on SQL Server 2008

Specialized on SQL Server Relational Engine, around Performance Tuning, Security, TSQL Source Management and Powerpivot.

13 years in the database industry . Worked on Large deployment of SQL 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
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 formation@evolusys ch

Special price

for Techdays attendees

Promo code : TRAINTDGVA

Trainingmore info on : formation@evolusys.ch

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
entity framework pour d veloppeur dba

EntityFramework pour développeur & DBA

ef advantages for developers
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
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
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
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
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
entity framework pour d veloppeur dba17

Entity Framework pour développeur & DBA

not yet available with ef
Not (yet) available with EF
  • Recursive CTEs
  • 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
dynamic sql considerations
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
Performance considerations
  • The behavior of EF queries are the exact same as Stored Procedures
    • Stored Procedures are compiled at first execution using the parameters values provided, plan is stored in cache. On second execution the plan is reused from cache
    • EF uses sp_executesql (as standard ADO.Net queries) and provide parameters… The behavior is the same, instead of the object_id (to find the cache entry of the plan) SQL Server use the query hash
  • The query engine is dynamic by essence, this allows execution plan to execute and adapt on the real data
sql profiler is your friend
SQL Profiler isyourfriend
  • 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
UsefullDynamic 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
  • Since SQL Server 2008 the query and plan hash help you to find similar queries with « changing » plans !
  • Contrary to the SQL Profiler, DMVs only show you queries that are in the Plan Cache
plan cache
Plan cache
  • As describe before the behavior of EF vs SP are similar but :
    • 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 : optimizefor ad hoc workloads’
      • The query plan iskept in cache onlyat 2ndexecution
  • If using different users to connect to the server, some other cache can suffer of contention
    • Really rare since SQL 2005 SP2
plan guides
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
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
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’)
entity framework pour d veloppeur dba28

Entity Framework pour développeur & DBA

  • 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.