Guidelines and Best Practices in Optimizing LINQ Performance

RELATED: "Understanding Parallel LINQ (PLINQ)" and "Essential LINQ"

Language Integrated Query (LINQ) is a queryexecution pipeline for use in the managed environment of .NET Framework. Inessence, LINQ is Microsoft's object relational mapper between your businessobjects and the underlying data sources and provides a simplified framework foraccessing relational data in an object-oriented fashion.

Although LINQ is great in the sense that you canquery data in your object model seamlessly, there are certain factors that youneed to consider to ensure that your application performs to the extent youneed it to. This article takes a look at some of the best practices that youcan follow for enhancing the performance of LINQ in your applications.

The Best Practices

Here are some of the best practices that you canfollow to boost your LINQ query performance.

      Objecttracking. Turn ObjectTrackingEnabled Property off if not required. Ifyou need only to read data through the data context and don't need to edit thedata, you should turn off ObjectTrackingEnabled property; doing so will turnoff the unnecessary identity management of objects and help boost theapplication's performance.

Here is an example:

using (TestDataContext dataContext = new TestDataContext()){dataContext.ObjectTrackingEnabled = false;}

Data Contexts

If there are multiple disconnected databases that you'reusing in your application, try using multiple data contexts to reduce theidentity management and object tracking overhead costs. You should attach onlythose objects to your data context that have been changed.

      Compiledqueries. You can use compiled queries to boost your application's performance.But remember that a compiled query could be costly when used for the firsttime. So, do ensure you use compiled queries only in situations where you needthem that is, when you need a query to be used repeatedly.

      Optimisticconcurrency. Concurrency handling is a mechanism that enables you todetect and resolve conflicts that arise out of concurrent requests to the sameresource at any point in time. Concurrency in ADO.NET is of two types: optimisticand pessimistic. LINQ follows an optimistic concurrency model by default. Youshould avoid using optimistic concurrency if not needed. To turn off the checkfor optimistic concurrency, you can use UpdateCheck.Never in the attributelevel mapping for your entity classes, as shown below:

[Column(Storage="_FirstName", DbType="NText",UpdateCheck=UpdateCheck.Never)]public string FirstName{get{return this._FirstName;}set{if ((this._FirstName != value)){this.OnFirstNameChanging(value);this.SendPropertyChanging();this._FirstName = value;this.SendPropertyChanged("FirstName");this.OnFirstNameChanged();}}}

      Retrieveselective data. You should use Take and Skip methods appropriately whenyou need to bind paged data to data controls. Here is an example:

private List GetStudentRecordss(int index, int size){using (TestDataContext dataContext = new TestDataContext()){return dataContext.Students.Take< Student >( size).Skip< Student >( index * size).ToList< Student>();}}

You should also filter down your required dataappropriately using DataLoadOptions.AssociateWith so that only the data that isrequired is returned. Here is an example that shows how you can use DataLoadOptions.AssociateWithto retrieve selective data in LINQ:

using (TestDataContext dataContext = new TestDataContext()){DataLoadOptions dataLoadOptions = new DataLoadOptions();dataLoadOptions.AssociateWith(emp=> emp.Department.Where(dept => dept.DeptCode == 1));dataContext.LoadOptions = dataLoadOptions;}

      Analyzequeries. You can also analyze how your LINQ queries have generated thecorresponding SQL statements and monitor them in the Visual Studio IDE. To dothis, you need to use the Log property of the data context as shown in the codesnippet below:

using (TestDataContext dataContext = new TestDataContext()){#if DEBUGdataContext.Log = Console.Out;#endif}

Suggested Readings

http://visualstudiomagazine.com/Articles/2007/11/01/Optimize-LINQ-to-SQL-Performance.aspx?Page=1

http://msdn.microsoft.com/en-us/magazine/cc721610.aspx

http://www.sidarok.com/web/blog/content/2008/05/02/10-tips-to-improve-your-linq-to-sql-application-performance.html

Joydip Kanjilal is leadarchitect for a company in Hyderabad, India, and is a Microsoft MVP in ASP.NET.He has authored Entity Framework Tutorial(Packt Publishing) and many other books and articles. Joydip blogs at aspadvice.com/blogs/joydip.