Wednesday, October 13, 2010

Linq Lamba Expressions Performance Problems SingleOrDefault Super Slow

A few months ago a release my website for real estate properties in Puerto Vallarta, Jalisco Mexico. In the last few nights I decided to start optimizing the site and noticed huge performance problems when using SubSonic 3 with Linq. I used to use SubSonic 2 on a high traffic site without any problems. However, with Linq I have been having High CPU Peaks and Memory consumption. I throw some webtests/Load tests with Visual Studio 2010 and started noticed the performance issues especially with Linq. No problems with Subsonic since its just a layer to Linq to Sql.

In particular I was surprise with the function .SingleOrDefault() on a single call for a Property it was taking 0.2 seconds on a database with ~4,000 records. The database has already been optimized.


I did some reading and found an article from JD in regards to this issue
I decided to implement on the Property class the static for delegates:

public static Func> _propertyGetQuery =
SubSonic.Linq.Structure.QueryCompiler.Compile((PropertyRentalDB db, int id) =>
from p in db.Properties
where p.PropertyId == id
select p);

public static Property GetById(int propertyId)
var db = new PropertyRentalDB();
Property p = _propertyGetQuery(db, propertyId).SingleOrDefault();
return p;

Compile and run Ants performance profiler and the load tests and the performance results here HUGE!


Total hits 71. Average from 0.20 to 0.011.

Now I can go and optimized more of the code where I use similar instances.
However, I think on my next project I will just go back and use SubSonic 2 and forget about Linq.