Best practices in optimizing LINQ performance

LINQ (Language Integrated Query) is a query execution pipeline that adds query capabilities to languages targeted at the managed environment of .Net. LINQ to SQL is an ORM (Object Relational Mapper) that maps your business objects and the underlying database providing you a simplified framework for accessing relational data in an object oriented manner. In this post, I’ll highlight a few tips and best practices that can be adopted to optimize LINQ performance.

Turn Object Tracking off

You should turn ObjectTrackingEnabled property off if you don’t need it. Note that if don’t need to change data but just read it, it is always advisable to turn this property off so as to turn off the unnecessary identity management of objects. The ObjectTrackingEnabled property is set to true by default. This implies that LINQ to SQL would keep track of every change that you make to your data so that it can remember those changes when you need to persist those changes to the underlying database at a later point in time. This will help boost the application’s performance to a considerable extent. The following code snippet illustrates how you can turn this property off (set it to false):

using (IDGDataContext dataContext = new IDGDataContext()) { dataContext.ObjectTrackingEnabled = false; //Usual code }

Turn Optimistic Concurrency off

Concurrency handling enables you to detect and resolve conflicts that arise out of concurrent requests to the same resource. Note that there are two types of concurrency – Optimistic and Pessimistic and LINQ follows an optimistic concurrency model by default. You should turn optimistic concurrency off unless it is needed. Note that UpdateCheck is set to Always which implies LINQ to SQL will check the property value, i.e., the value contained in that property against the value stored in the column of the database table that is mapped to that property.

You should avoid using optimistic concurrency if not needed. You can use the UpdateCheck property to turn off optimistic concurrency. The following code snippet illustrates how you can set this property in the attribute of your entity class:

[Column(Storage="_Address", DbType="NText", UpdateCheck=UpdateCheck.Never)]

Use Compiled Queries judiciously

You can take advantage of Compiled Query to boost query performance in your application. But, remember that compiled query could be costly when used for the first time. So, do ensure you use compiled queries only in situations where you need them, i.e., when you need a query to be used repeatedly.

At the time when a query is to be executed by the LINQ engine, LINQ to SQL translates the LINQ queries to SQL — this is repeated every time the query is to be executed. This involves traversing the expression tree recursively again and hence it is a performance overhead. No worries at all — you have the CompiledQuery class for the rescue.

You can leverage CompiledQuery to eliminate this performance overhead for queries that need to be executed again and again. A word of caution though: Use CompiledQuery judiciously and only when it is needed.

Other optimization tips

You should not dump all the database objects into one single DataContext. The DataContext should represent one unit of work. This approach if followed would reduce the identity management and object tracking overhead involved. Also, you should only attach the objects to your data context those have been changed since the time they were read into the memory.

You should always analyze your LINQ queries and take a monitor the generated SQL. You can set the Log property of the data context to see the generated SQL.

using (IDGDataContext dataContext = new IDGDataContext()) { dataContext.Log = Console.Out; }

This will help you to understand how your LINQ query has been translated to SQL and any additional columns or extra data that is retrieved when the SQL query is eventually executed.

You should retrieve only the data that is needed and avoid retrieving all of the data. To achieve this, you can take advantage of the Take and Skip methods. You can also filter the data to be retrieved using DataLoadOptions.AssociateWith so that only the required data is returned. The following code snippet illustrates how DataLoadOptions.AssociateWith can be used.

using (IDGDataContext dataContext = new IDGDataContext()) { DataLoadOptions dataLoadOptions = new DataLoadOptions(); dataLoadOptions.AssociateWith<Customer>(customer => customer.Address.Where<Address>(address => address.PinCode == 500016)); dataContext.LoadOptions = dataLoadOptions; }

You should also consider the Entity Identity Management overhead when writing your LINQ queries. Let me explain this with an example. Consider the following query.

using (IDGDataContext dataContext = new IDGDataContext()) { var data = from a in dataContext.Authors select data; }

Now, if you replace the above query with the one given below, the query performance would be much faster.

using (IDGDataContext dataContext = new IDGDataContext()) { var data = from a in dataContext.Authors select new Author { AuthorID = a.AuthorID, FirstName = a.FirstName, LastName = a.LastName }; }