10 Tips to improve Linq2Sql performance

Good day.
If you are going to use LINQ in projects, you should learn how to speed up the work of this technology.

The points:

1. Disable ObjectTracking - we are already in use.
2. Spread unrelated tables by different datacontext. Reducing the size of the data context will reduce the amount of used memory and operations to control changes to objects.
   
3. Use CompiledQuery - I think there will be a gain, just what? Here you can see the results of compiled vs uncompiled LINQ queries.
   In a nutshell - there is an increase, depending on the frequency of similar requests from 10% to 70%
   
   Example of a compiled query:
   Func <NorthwindDataContext, IEnumerable <Category >> func =
   CompiledQuery.Compile <NorthwindDataContext, IEnumerable <Category >>
   ((NorthwindDataContext context) => context.Categories.
   Where <Category> (cat => cat.Products.Count> 5));
   
   Further, you can create a static class with a set of these compiled queries:
   /// <summary>
   /// Utility class to store compiled queries
   /// </ summary>
   public static class QueriesUtility
   {
   /// <summary>
   /// Gets the query for returns products.
   /// </ summary>
   /// <value> The query containing the categories. </ value>
   public static Func <NorthwindDataContext, IEnumerable <Category >>
   GetCategoriesWithMoreThanFiveProducts
   {
   get
   {
   Func <NorthwindDataContext, IEnumerable <Category >> func =
   CompiledQuery.Compile <NorthwindDataContext, IEnumerable <Category >>
   ((NorthwindDataContext context) => context.Categories.
   Where <Category> (cat => cat.Products.Count> 5));
   return func;
   }
   }
   }
   ')
   The use of this class will be as follows:
   using (NorthwindDataContext context = new NorthwindDataContext ())
   {
   var categories = QueriesUtility.GetCategoriesWithMoreThanFiveProducts (context);
   }
   
   In addition, maintaining many queries in one place will avoid duplicating code and easier support.
   
   
4. Using DataLoadOptions.AssociateWith - the point is not to use LazyLoading, but load the related tables immediately. But to load not all data, but only on a certain condition.
   
   using (NorthwindDataContext context = new NorthwindDataContext ())
   {
   DataLoadOptions options = new DataLoadOptions ();
   options.AssociateWith <Category> (cat => cat.Products.Where <Product> (prod =>! prod.Discontinued));
   context.LoadOptions = options;
   }
   
   
5. Use Optimistic concurrency - add a TimeStamp type field to each table - thus LINQ itself will be responsible for concurrency. In addition, using this approach, you can transfer an entity from one datacontext. If the Optimistic Concurrency application is not needed, it can be disabled. In the properties of the Entity in the designer, set UpdateCheck equal to UpdateCheck.Never
   
   
6. Monitor queries generated by LINQ. Most of the queries will be generated on the fly, so, like most of the MS design generators, LINQ can generate a not entirely optimal query — pull up extra columns, for example. Logging is very simple - using (NorthwindDataContext context = new NorthwindDataContext ())
   {
   context.Log = Console .Out;
   }
   
   At work, we use DataContextManager, through which we create all datacontexts, so it will be even easier to bind logging to the entire application:
   internal static class DataContextManager
   {
   public static DataContextType Create <DataContextType> ( bool readOnlyAccess)
   where DataContextType: DataContext, new ()
   {
   DataContextType dc = new DataContextType ();
   // DebugWriter is a TextWriter that writes to DebugInfo.txt file
   dc.Log = Common.Logging.Logger.DebugWriter;
   dc.ObjectTrackingEnabled =! readOnlyAccess;
   dc.DeferredLoadingEnabled = false ;
   return dc;
   }
   }
   
   
7. Use the Attach method only when you really need it. For example, do not use AttachAll for collections, but check each object in the collection for changes and bind / not bind it.
8. Be more attentive when working with object change control. When working with datacontext in read mode, simple queries can create additional resource costs. For example, a very simple query:
   using (NorthwindDataContext context = new NorthwindDataContext ())
   {
   var a = from c in context.Categories
   select c;
   }
   
   However, this query will spend more resources than the following:
   using (NorthwindDataContext context = new NorthwindDataContext ())
   {
   var a = from c in context.Categories
   select new Category
   {
   CategoryID = c.CategoryID,
   CategoryName = c.CategoryName,
   Description = c.Description
   };
   }
   
   Why? Because in the first Object Tracking is still working, while in the second LINQ just gives you objects and forgets about them.
   
   
9. Get only the required number of lines using the Take and Skip methods. Standard script for paging:
   
   /// <summary>
   /// Gets the products page by page.
   /// </ summary>
   /// <param name = ”startingPageIndex”> Index of the starting page. </ param>
   /// <param name = ”pageSize”> Size of the page. </ param>
   /// <returns> The list of products in the specified page </ returns>
   private IList <Product> GetProducts ( int startingPageIndex, int pageSize)
   {
   using (NorthwindDataContext context = new NorthwindDataContext ())
   {
   return context.Products
   .Skip <Product> (startingPageIndex * pageSize)
   .Take <Product> (pageSize)
   .ToList <Product> ();
   }
   }
   
   "Premature optimization is the root of all evil." That said Donald Knut.
   So be careful, especially when using CompiledQuery. LINQ queries are not compiled as Regex. Compiling a LINQ query creates an in-memory object that already has a SQL query and a delegate to work with it.
   
   In principle, Knut's words refer to any optimizations, so it’s not worthwhile to optimize everything in succession. The best way out is to try the approach and see if it brings real benefits.
   
   - allows you to quickly write code with LINQ and analyze its implementation and results