Archive for February, 2008

h1

LinQ to SQL Bad Performance?

February 13, 2008

This blog is about a recent finding I had with a readify colleague the other day on a fairly large asp.net project we have been working on for 6 months now and have come towards the end of the scrum iterations and into the “Security and Performance Review” sprint.

On a dual core 2.16ghz laptop running vista 64 bit with 4 gig of ram and a 7200rpm hard drive the results were about 5 to 8 requests per second with the CPU being the obvious bottle neck spiking to 100%. The Load Testing was performed through visual studio 2008 on the same laptop so part of the performance is partially blamed there. We tried running the asp.net application on a server and running the load tests and web tests on a different machine but got more or less the same results.

We created some Data Priming Harness to insert multiple rows into each of our tables before the test so that we could have some data in the database and the inserts were happening at an extremely slow rate with again to CPU being shot up to 100%.

 

We ran SQL Profiler and found that on pages like our home page where we list a list of countries and cultures that there were 800 hits to the database. This could have been done with 1 database hit by just doing a select * from country and joining on Culture where country.CultureID = culture.ID. Obviously we have done something seriously wrong to cause 800 hits to the database just to load the homepage of our project once and do nothing more than display a list of countries and cultures right? So We look at the aspx page and see our repeater control on it. The repeater control has some in line code blocks in it like this:

repeater 

and now the drum roll…. ready for what the code behind looks like?

codeBehind 

 

Okay. So it’s not the most complicated stuff in the world. So the question still remains. Why does SQL Profiler show there are 800 hits to the database? Well. The answer is:

You need to use Data Transfer Objects to stop the calls from going to the database and instead retrieve it from memory. The line of code

<%# Eval(“Name”) %> in the Repeater is causing a hit to the database for each row!

To stop this create a class to hold your data froSQLql, maybe something like this:

public class ContentManagementLink

{

public int ID { get; set; }

public int SortOrder { get; set; }

public string Url { get; set; }

public string Description{ get; set; }

public int Name { get; set; }

}

 

and basically from youdata sourcece library you would do something like this:

 

private static List<ContentManagementLink> GetLinksOfSubPages(int sectionID, int cultureID)

{

PageManager pageManager = new PageManager();

var pages = (from page in pageManager.GetPagesBySectionID(sectionID, cultureID)

where page.PublishStatusID == (int)PublishStatusID.Online

select page).ToList();

var formattedPages = (from page in pages

select new ContentManagementLink

{

Url = LinkService.GetPageLink(sectionID, page.ID, cultureID),

Text = page.Title,

SortOrder = page.SortOrder,

ID = page.ID,

Name = page.Name,

EntityType = EntityType.Page

}).ToList();

return formattedPages;

}

 

What I have learned from this is that ORM’s can be easy to use the wrong way and that it’s very easy to forget that a call to an object’s property may very well be resulting in a database call.

 

I am still not certain as to why the CPU is getting spiked to 100% by aspnet_wp rather than sql hogging the hard drive and creating the application to be I/O bound. My guess is that linq to sql query generation can be expesive.

 

So I guess at the end of the day I haven’t solved the solution completely but I have found that there is something fundementally wrong with the way we have implemented LinQ and that it is more myself to blame then LinQ.

 

More to come on this topic. I would appreicate any feed back on similair experiences other people may have had.