Performance Tips (LINQ to SQL)

One part of the application that I am working on is performing poorly. According to the performance measurement that I get from using EQATEC profiler, it takes 3-4 seconds to load this page.

IIS / UI 
Since this is a web application, so I made sure all the IIS/non-application performance enhancements are in place:

• IIS compression are turned on
• Javascript files are combined and compressed
• CSS files are combined and compressed
• Images, scripts, and CSS files are cached
• Conforming to YUI rules & Google PageSpeed performance rules

Setting all those boost some performance in my application - not really that noticeable in normal usage. It cut down about 0.5 seconds or about 13% or my load time. So now we are in 2.8-3.5 seconds range. So what else?


DATA ACCESS LAYER
Since all those are set properly, so now I am tracing down into the application itself - and eventually narrowed it down to a database call that somehow is costing 85% or the load time. On the surface there seems to be nothing wrong with the query itself - it's just a regular select statement from some tables (joined) with some given criteria (where clause) - constructed in LINQ.

DATABASE INDEXES / KEYS / STATS
So the next step was getting the correspondent SQL statement generated by the LINQ statement and run it through SQL query analyzer to make sure that the tables queried have proper keys, indexes, etc. Adding some indexes and stats really enhance the performance in table queries - which according to SQL DB Engine Tuning Advisor should give about 78% improvement. In real usage, it cut down about 1.5 second of load time. So now we are at 1.2-1.9 seconds range - which is not bad compared to the earlier measurement.

LINQ COMPILED QUERIES
So now I tried to do some compiled queries in my LINQ to SQL queries. That combined with data load options proof to be working better as far as performance. My load time goes down more to 0.8-1.4 seconds. So we are getting into the sub-seconds range now - coming down from 3-4 seconds. Major improvement!

PARALLEL LIBRARY
Some of my LINQ queries are LINQ to objects (instead of LINQ to SQL), so I try to put them into parallel mode when possible using Parallel Library. There are several areas where I did this and it yields faster results (I am running a dual core machine, the production VM is also the same way). The average load time is now at 0.7-1.2 seconds. Small improvement (0.1 or 0.2 seconds - but it's 10% improvement!).

STORED PROCEDURES
I am pretty much set and ready to commit my changes when I remember reading an article about stored procedures - which is still faster than LINQ to SQL or even compiled queries. So I have got to try that! So I commented out the call to my compiled queries and move the queries into stored procedure instead. Made some adjustments in my LINQ to SQL project and rebuilt. Unbelievable! My load time now is 0.6 to 0.9 seconds. It never even break 1 second.

So there you have it. Some optimization tips. Just to recap:

1. Use a profiler to measure. For UI/CSS/Javascript etc you can use Firebug, Google PageSpeed. I use EQATEC profiler to do tracing and find bottlenecks in my applications.I blogged about EQATEC before here.
2. Check your queries - are they making unnecessary joins, missing where clauses, etc?
3. DB indexes, keys, stats - are they set?
4. Using compiled queries in LINQ does make a difference
5. Make use of the Parallel Library if applicable
6. Stored procedures still trumps everything else - use only when necessary to avoid code separation