Profiling LINQ Queries

When we are implementing LINQ queries to retrieve data from our database server, it's a good practice profile the queries through SQL Profiler tool, it might be decisive for improving the query and reducing the time responses

In this example we have our DataContext pointing to a database with a table named B_PRODUCTS, filled with 30000 records for this example

In order to measure the time response of this query into the server side, we will begin a new instance of SQL Profiler tool, and begin a new trace connected to our server

We will make use of the template T-SQL duration, for targeting the duration of the query in milliseconds when is processed by the server. In the events tab of the trace, we can filter the trace log only to those events we want to register

At this point, we run the trace, and launch the query

This is the initial linq query

     
	using (BusinessClassesDataContext context = new BusinessClassesDataContext())
    {
        Products = (from prods in context.B_PRODUCTs
                    orderby prods.Description ascending
                    select prods).ToList<B_PRODUCT>();
    }
    

Now we will include a where clause with 2 conditions and check the trace log

     
		Products = (from prods in context.B_PRODUCTs
                where prods.Description.EndsWith("kit")
                    && prods.IdProduct > 100
                orderby prods.Description ascending
                select prods).ToList<B_PRODUCT>();
                

Now we will change the implementation of the first condition making use of the SqlMethods namespace, the which one includes SQL expressions for querying data

     
		Products = (from prods in context.B_PRODUCTs
                where SqlMethods.Like(prods.Description, "%kit")
                    && prods.IdProduct > 100
                orderby prods.Description ascending
                select prods).ToList<B_PRODUCT>();

With this second approach, we can appreciate a reduction of the time response of 40 milliseconds, with this information we can leverage the most efficient way to query our data


<METHOD SOFTWARE © 2013>