LINQ to SQL Performance and Prepared Statements
by David Hayden ( Microsoft MVP C# ), Filed: LINQ to SQL
Rico Mariani has an interesting series of posts on LINQ to SQL Performance with Part 4 giving some numbers on the performance of LINQ to SQL compared to raw ADO.NET ( DataReader ).
I don't know if you can read a lot into those numbers other than 1) there has been a lot of improvement in LINQ to SQL performance and 2) there are some ways to improve LINQ to SQL performance- like using compiled queries when running the same query many times.
What got me thinking in the post was not the performance numbers, but this simple statement:
“However I didn't go to the trouble of creating prepared statements for update and insert cases, DLinq gives you this automatically.”
Interesting statement that suggests calling SqlCommand.Prepare() is a lot of effort. Huh? Makes me now wonder two things:
- What his insert and update commands look like using straight ADO.NET?
- What the performance numbers would look like if he did use prepared statements?
I am under the impression that SQL Server 2005 is pretty smart about handling and caching execution paths, etc. so the use of prepared statements is not as dramatically beneficial as it may be in earlier versions of SQL Server. However, this is something I would have loved to see Rico test just to see if there was a difference.
I now feel those “DLINQ is Faster” observations for update, compiled update, and insert are less interesting since I don't have the prepared statement tests. Is use of prepared statements why LINQ to SQL is faster in those scenarios? Seems incomplete...
Source: David Hayden ( Microsoft MVP C# )
Filed: LINQ to SQL