Linq Lamba Expressions Performance Problems SingleOrDefault Super Slow

A few months ago a release my website www.puertovallarta-bienesraices.com 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

http://www.jdconley.com/blog/archive/2007/11/28/linq-to-sql-surprise-performance-hit.aspx

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.