Use CompiledQuery.Compile to improve LINQ to SQL performance
- by Michael Freidgeim
After reading DLinq (Linq to SQL) Performance and in particular
Part 4 I had a few questions.
If CompiledQuery.Compile gives so much benefits, why not to do it for all Linq To Sql queries? Is any essential disadvantages of compiling all select queries? What are conditions, when compiling makes whose performance, for how much percentage? World be good to have default on application config level or on DBML level to specify are all select queries to be compiled? And the same questions about Entity Framework CompiledQuery Class.
However in comments I’ve found answer of the author ricom
6 Jul 2007 3:08 AM
Compiling the query makes it durable. There is no need for this, nor is there any desire, unless you intend to run that same query many times.
SQL provides regular select statements, prepared select statements, and stored procedures for a reason. Linq now has analogs.
Also from 10 Tips to Improve your LINQ to SQL Application Performance
If you are using CompiledQuery make sure that you are using it more than once as it is more costly than normal querying for the first time. The resulting function coming as a CompiledQuery is an object, having the SQL statement and the delegate to apply it. And your delegate has the ability to replace the variables (or parameters) in the resulting query.
However I feel that many developers are not informed enough about benefits of Compile.
I think that tools like FxCop and Resharper should check the queries and suggest if compiling is recommended.
Related Articles for LINQ to SQL:
MSDN How to: Store and Reuse Queries (LINQ to SQL)
10 Tips to Improve your LINQ to SQL Application Performance
Related Articles for Entity Framework:
MSDN: CompiledQuery Class
Exploring the Performance of the ADO.NET Entity Framework - Part 1
Exploring the Performance of the ADO.NET Entity Framework – Part 2
ADO.NET Entity Framework 4.0: Making it fast through Compiled Query
