Using Dapper.NET to Boost DAL Performance

Lately, my focus has been to optimize and increase the performance of our web-application. This, of course, includes load time and speed - among other things. There are a lot of performance enhancements that we can do to speed up our load time, from the UI stack (javascript loading, CDN, minimization, etc), DB stack (indexing, query caching, stored procs, etc), application/biz layer stack (algorithm optimizations, data storage, caching, etc).

Now, our web-app is using Linq-to-SQL as our DAL and since a year ago, we have been doing several things to speed up our DAL - by using compiled queries, creating stored procs, views, etc. One of the tools or library that I have been using to boost DAL performance is Dapper.NET.

Dapper is created by the Ninjas working in the Stack Overflow team - and although it is somewhat goes back into writing straight SQL queries again in your code, but the performance gain is quite significant. Yet, it also allows you to retain the object mapping like in Linq-to-SQL.

I am not going to lay out all the functionality and the capability or the performance matrix of the Dapper.NET library. You can read them on your own here. But, I will lay out some of the things that have been very very useful for our project.

1. Some of our queries requires some joins and doing the joins in LINQ is a bit slow compared to a simple join statement in the straight SQL. Sometimes we go all the way to make view for the join and encapsulate the call in a stored procedure, and then call it using Dapper.
2. For any LINQ that uses "CONTAINS", we revert to Dapper (using "IN" in straight SQL).
3. Multiple results query. This actually something that Dapper allows us to do. Previously we have to make several query calls to get multiple results. But with Dapper, we can make 1 call.

Using Dapper.NET results in a lot of performance enhancements in our web-application. There was a page that now loads within 250 ms, which previously loaded in more than a second. So, we definitely see major improvement suggested by the SO team.

If you have never tried Dapper.NET - I urge you to check it out.

Further reading:
Dapper.NET Code in Google Code
Sam Safron's Blog