Posted by: advapi | September 10, 2008

From Dev to Dev : LINQ Performance optimization

It has been a quite long time since last time I wrote about coding……
Tomorrow I worked on refactoring an page I wrote a month ago, since it started loading slow…
I can’t paste you the code but consider this scenario :

3 DropDown containing Languages , Categories, Subcategories
A gridview with a lot of data and on each row three columns with the dropdowns described above

The Dropdowns and Gridview are filled with the use of Linq tecnology.

Step 1

On each RowDataBound event  I was using LINQ in that way :

var res = from o1 in datacontext.Categories select o1;

dropdown.DataSource = res;


// update dropdown values as the one taken from the DB

This started being a really pain in the ass as soon as the categories and subcategories started increasing!!

Step 2

I started profiling using jetbrains dotTrace ( Jetbrains developers are cool!, Resharper and dotTrace rulez!) and found that the internal LINQ queries to DB were really a lot of , and also consider that declaring them in that way took the CLR to create the LINQ query tree each time it finds the declaration var…. so I remembered that the dropdown list is the same for all the rows of the datagrid… and I can filter the subcategories starting from the category dropdown value….. so I created a method in the Page_Load that does almost this

List<Category> listCat = GetCategories();

List<SubCategory> listSub = GetSubCategories();

List<Language> listLang = GetLanguages();

those generics are defined as private static inside the class body, here are used just for description

Each time I need to access the item in order to bind or take a subbset I use those collections instead of doing the linq call…..

Well the results are above my expectations…… for example the time spent for loading each dropdown of subcategories passed from 242ms to 48ms!

Step 3

As Madonna said a long time ago : “I want more!”, so the other step was for the subcategory dropdown not to use the

var resSubCat = from o1 in listSubwhere o1.IDCategory.Equals(dropdownCategory.SelectedValue) select o1;

but instead I used the Compiled Query

CompiledQuery is a way Linq provides to build the linq query three and to call it passing the parameters without parsing each time the var….. conditions….

It uses the lambda expression (introduced with C# 3.0) to provide flexibile way of writing queries (more on lambda on another post I’ll write soon)

By the way you can define a CompiledQuery in this way

var compiledQuery = CompiledQuery.Compile((DataContext dc, int idCategory)=>from o1 in listSubwhere o1.IDCategory.Equals(idCategory) select o1);

I don’t have the sourcecode under my hands by the way it should be ok…. anyway long life to Linq, c# 3.0 and so on!

Stay tuned….. more to come!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: