What type of Linq queries for SQL should be compiled?

Question

I use Linq to SQL in the project and read articles about how inefficient the structure is. I read that one way to dramatically increase performance is to create compiled queries. Will all queries compile better? Or are there certain cases where this may not be of particular importance? I assume this is important for large scale applications that receive a lot of traffic.

Thanks.

Answer 1

I have come across this before. Compiling queries can be a great thing and greatly improves performance. This is not always the best solution.

I worked on some reports that took 30-40 minutes. I found that we called certain requests hundreds and thousands of times. Compiling queries has provided improvements, but not enough.

As a result, I made fewer queries that returned more data. These are fewer open and close connections. The results were messages that took place in less than a minute.

So, if you need to run the query several times, compilation is a great option. If it works hundreds or thousands of times, you may need a new approach.

A few posts about these two things:

Optimizing SQL queries for reports: another 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/

Another thing to consider is the indexes in your database. Your request is slow, but you don’t know why it is slow. If this is a search on a column that is not indexed, this may also be your problem.

Answer 2

Beware of premature optimization!

Until you profile your application and decide that you spend a large chunk of time creating queries, it does not cost the developer time and reduce simplicity, convenience and flexibility.

Even if you spend a lot of time building queries, keep in mind that pre-compiling queries will reduce their run time by a relatively small constant, rather than by orders of magnitude. So start by looking at procedural changes (such as Narnian mentions) or caching techniques to reduce the number of times you really need to complete these requests in the first place.

As soon as you do this and still find that there is a bottleneck in the compilation of the request, it is likely that 90% of your time will be spent on about 5% (or less) of the requests you use. Optimize only these queries.

Update

In modern implementations of the Entity Framework, query caching is now integrated, so using the modern version of .NET, you probably get 90% of the benefits that pre-compiled queries offered you anyway. Moreover, do not worry about changing the structure of the code to provide a theoretical increase in performance.

Answer 3

If you have similar queries running over and over, you can increase performance by compiling them.

There are some caveats: http://omaralzabir.com/solving_common_problems_with_compiled_queries_in_linq_to_sql_for_high_demand_asp_net_websites/