For the past few months at work I have been working on importing records from one system to another. For the data access we have been using Ling to SQL. For the one system we had to perform some pretty tricky queries to make sure we got only non deleted records. To do this for all the tables we had pretty much the same query everywhere in our class, modified of course to get the different types out. When we started running the program to moves records the first thing we noticed was that it was pretty slow, and by slow I mean a record every couple seconds. Since we were processing tens of thousand of records we needed to find a way to speed this up. Our first thought was to put indexes on the database but this yielded no noticeable gain at all. We weren’t doing anything fancy in the code so I decided to do a quick Bing search for link performance tips. The search returned tons of links to blogs with 5 or 10 tips on how to improve the performance. Some of the tips didn’t seem to be relevant or feasible for our application but the one tip that I kept seeing was compiling your queries.

I had read about being able to compile your queries but never thought of it as a practical solution. Being skeptical I started to compile some of my queries and and then ran the program with and without the compiled queries. To my surprise the program run much faster with the compiled queries. Having proved that they were indeed faster I decided to replace all of my inline queries with compiled ones. After replacing all the queries we started importing records again. The performance increase was noticed immediately, we went from an average of one every two seconds to about 2 every second. I read that this could speed up performance 3-5 times which seemed to be the case here.

What is happening with compiled queries, or at least what I think is happening, is that the first time the query is run the expression tree is created and the next time it is run all it has to do is plug in the parameter(s) from the delegate. This way the expression tree only has to be created once, which is why this is great for queries that will be run over and over in your application.

 

Here are a couple links I used find this info:

http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

http://davidhayden.com/blog/dave/archive/2008/02/19/HighPerformanceLINQToSQLCompiledQueriesORMappersEcommerceWebsites.aspx