Telerik blogs

LINQ Tip of the Week: Optimizing query execution by using parameterized queries

July 21, 2009 Productivity 0 Comments


This is the first post of the series that have the goal to help Telerik OpenAccess ORM users enhance their experience in Linq against OpenAccess persistent objects.
The two areas where performance enhancement can be taken advantage of are reusing a Linq query from the query cache and altering the query in a way where bigger portions of it are executed on the server. Today we will share with you a tip about the first one.


When a Linq query is requested for execution, the query goes through a step of compilation – where the Linq query expression tree is transformed to an SQL expression tree. After that step is completed Telerik OpenAccess ORM uses the query cache to keep the already compiled Linq queries, so it can reuse them for further execution.


How can we take advantage of this? There are countless scenarios where two queries are very similar but are used for completely different purposes in an application. For example: the manager wants to see the data for all his employees from the sales team. For the purpose he/she would execute the following method:

        static IQueryable GetAllSalesAssociates(IObjectScope scope)
       {
           var employees = from e in scope.Extent<Employee>()
                                     where e.Title == "Sales Associate"
                                     select e;
           return employees;
       }

Another module of the application, shows confidential information about the Owners. It queries the Employee table using the following method:

        static IQueryable GetOwners(IObjectScope scope)
       {
           var owners = from e in scope.Extent<Employee>()
                                where e.Title == "Owner"
                                select e;
           return owners;
       }

 

Although very similar, these queries generate different expression trees. That always leads to two compilation steps. This is the moment where little refactoring should be done and parametrized queries used. Consider the following method:

        static IQueryable GetEmployees(IObjectScope scope, string title)
       {
           var employees = from e in scope.Extent<Employee>()
                                     where e.Title == title
                                     select e;
           return employees;
       }

When this method is called twice, once per application module, you guessed – compilation of the Linq query is performed only ones. The seccond time this is called, Telerik OpenAccess ORM reuses the compiled query from the query cache and steps over the expression tree traversal step.
There are numerous queries that can be used as example from the Telerik OpenAccess ORM 101 Linq Samples available in the Telerik OpenAccess ORM code-library.

Telerik Data Access

Comments

Comments are disabled in preview mode.