Speeding up page loading "“ Part 4

In the first three parts of this series we looked at JavaScript and CSS optimizations, image reduction and query reduction. In this part we’ll look at some options around optimizing the actual queries. I should warn you straight up that I am not a DBA. I’m only going to suggest the simplest of things, if you need to do some actual query optimizations then please consult with somebody who really understands this stuff like Mike DeFehror Markus Winand.

The number of different queries run on a site is probably smaller than you think. Certainly most pages on your site are, after the optimizations from part 3, going to be running only a couple of queries with any frequency. Glimpse will let you know what the queries are but, more importantly, it will show you how long each query takes to execute.

Without knowing an awful lot about the structure of your database and network it is hard to come up with a number for how long a query should take. Ideally you want queries which take well under 100ms as keeping page load times is important. People hate waiting for stuff to load, which is, I guess, the whole point of this series.

Optimizing queries is a tricky business but one trick is to take the query and paste it into SQL Management Studio. Once you have it in there click on the actual execution plan button. This will show you the execution plan which is the strategy the database believes is the optimal route to run the query.

If you’re very lucky the query analyser will suggest a new index to add to your database. If not then you’ll have to drill down into the actual query plan. In there you want to see a lot of

Index seek

Index seeks and index scans are far more efficient than table seeks and scans. Frequently table scans can be avoided by adding an index to the table. I blogged a while back about how to optimize queries with indexes. That article has some suggestions about specific steps you can take. One which I don’t think I talk about in that article is to reduce the number of columns returned. Unfortunately EF doesn’t have support for selectively returning columns or lazily loading columns. If that level of query tuning appeals to you then you may wish to swap out EF for something like Dapper or Massivewhich are much lower level.

If you happen to be fortunate enough to have a copy of Visual Studio ULTIMATE (or, as the local Ruby user group lead calls it: Ultra-Professional-Premium-Service-Pack-Two-Release-2013) then there is another option I forgot to mention in part 3. IntelliTrace will record the query text run by EF. I still think that Glimpse and EFProf are better options as they are more focused tools. However Glimpse does sometimes get a bit confused by single page applications and EFProf costs money so IntelliTrace will work.