What type of Linq to SQL queries should be compiled?

Question

I'm using Linq to SQL on a project and have read articles about how inefficient the framework is. I've read that one way to drastically increase the performance is to create compiled queries. Would all queries perform better compiled? Or are there certain instances where it may not make much of a difference? I assume this is critical on large scale applications that get a high volume of traffic.

Thanks.

Answer 1

I've run into this before. Compiling queries can be a great thing and does improve performance significantly. That is not always the best solution though.

I was working on some reports that took 30-40 minutes to run. What I found was that we were calling certain queries hundreds and thousands of times. Compiling the queries did give improve things, but not nearly enough.

What I ended up doing was running less queries that returned more data. This is less opening and closing connections. The results was reports that ran in less than a minute.

So, if you're having to run a query a few times, compiling it is a great option. If it's running hundreds or thousands of times, you may just need a new approach.

A few posts regarding these two things:

SQL Query Optimization for Reporting: a Different Approach:

http://www.foliotek.com/devblog/sql-query-optimization-for-reporting-a-different-approach/

Unexpected Benefits of Precompiled Queries in Linq:

http://www.foliotek.com/devblog/unexpected-benefits-of-precompilation-of-linq/

One more thing to consider would be the indexes on your database. Your query is slow, but you don't know why it's slow. If it is searching on a column that is not indexed, then that could be your problem, too.