LINQ to SQL Performance Optimizing revisited

In my previous blog post LINQ to SQL Performance Optimizing, I demonstrated a few ways of trying to improve performance for loading hierarchies with LINQ to SQL. I’m assuming that these methods apply to Entity Framework and NHibernate also.

What I showed previously

I demonstrated how the default method for getting data (lazy load) can be extremely detrimental to performance because it can execute a very large number of queries against the database.

I also demonstrated that using LoadOptions can be worse than not using LoadOptions, because the results are joined together forcing a huge amount of redundancy into the result set.

Lastly, I demonstrated that using propertly selected LoadOptions, performance can be improved significantly.

I also hinted at a third options (lazy load and load options being the first two options).

Full Load

The third option is to create a stored procedure that does a full load of all the data that you need for your report/hierarchy. A stored procedure can return any number of result sets, and a LINQ to SQL mapping of a stored procedure can retrieve these result sets. Unfortunately, they’re not correctly “connected” to eachother, so we have to do that “by hand”. But the performance is significantly better, in my demo case, it’s twice as fast as the previous champion!

Method Execution time Queries Query text
Default/Lazy Load 1.4 s 1031 queries 448kb
Naive Load Options 2.4 s 31 queries 77kb
Smarter Load Options 0.45 s 78 queries 114kb
Full Load 0.137s 1 query 1kb (actually 195 bytes)

(for details on the other methods, see here)

Actually, the new method is 3.28 times faster!!

It comes at a steep price

  • You must create a stored procedure that retrieves the data you need, and you need to maintain this stored procedure
  • The data you retrieve might be read only for your purposes, you need to test this (it might not be properly attached to the context that spawned it)
  • You must load the data with a custom made data context method
  • You must assemble your hierarchy “by hand”

I’m hoping to at least partially automate this process, but for now, we’re stuck doing it by hand. Below you’ll find the code used for this test. As you’ll see, it’s messy compared to lazy load or load options.


The Code

Here’s the stored procedure I’m using, it’s based on a northwind database;

CREATE procedure [dbo].[GetSuppliers]
  -- Supplier 
  declare @dbo_Suppliers TABLE (
   [SupplierID] Int NOT NULL,
   [CompanyName] NVarChar(40) NOT NULL,
   [ContactName] NVarChar(30),
   [ContactTitle] NVarChar(30),
   [Address] NVarChar(60),
   [City] NVarChar(15),
   [Region] NVarChar(15),
   [PostalCode] NVarChar(10),
   [Country] NVarChar(15),
   [Phone] NVarChar(24),
   [Fax] NVarChar(24),
   [HomePage] NText)

  insert into @dbo_Suppliers
  select  c.[SupplierID], c.[CompanyName], c.[ContactName], c.[ContactTitle], c.[Address], c.[City], c.[Region], c.[PostalCode], c.[Country], c.[Phone], c.[Fax], c.[HomePage] from dbo.Suppliers as c

  select * from @dbo_Suppliers

  -- Product 
  declare @dbo_Products TABLE (
   [ProductID] Int NOT NULL,
   [ProductName] NVarChar(40) NOT NULL,
   [SupplierID] Int,
   [CategoryID] Int,
   [QuantityPerUnit] NVarChar(20),
   [UnitPrice] Money,
   [UnitsInStock] SmallInt,
   [UnitsOnOrder] SmallInt,
   [ReorderLevel] SmallInt,
   [Discontinued] Bit NOT NULL)

  insert into @dbo_Products
  select  c.[ProductID], c.[ProductName], c.[SupplierID], c.[CategoryID], c.[QuantityPerUnit], c.[UnitPrice], c.[UnitsInStock], c.[UnitsOnOrder], c.[ReorderLevel], c.[Discontinued] from dbo.Products as c
  join (select distinct SupplierId from @dbo_Suppliers) as p on  c.SupplierId = p.SupplierId 

  select * from @dbo_Products

  -- Category 
  declare @dbo_Categories TABLE (
   [CategoryID] Int NOT NULL,
   [CategoryName] NVarChar(15) NOT NULL,
   [Description] NText,
   [Picture] Image)

  insert into @dbo_Categories
  select  c.[CategoryID], c.[CategoryName], c.[Description], c.[Picture] from dbo.Categories as c
  join (select distinct CategoryId from @dbo_Products) as p on  c.CategoryId = p.CategoryId 

  select * from @dbo_Categories

  -- Order_Detail 
  declare @dbo_OrderDetails TABLE (
   [OrderID] Int NOT NULL,
   [ProductID] Int NOT NULL,
   [UnitPrice] Money NOT NULL,
   [Quantity] SmallInt NOT NULL,
   [Discount] Real NOT NULL)

  insert into @dbo_OrderDetails
  select  c.[OrderID], c.[ProductID], c.[UnitPrice], c.[Quantity], c.[Discount] from dbo.[Order Details] as c
  join (select distinct ProductId from @dbo_Products) as p on  c.ProductId = p.ProductId 

  select * from @dbo_OrderDetails

  -- Order 
  declare @dbo_Orders TABLE (
   [OrderID] Int NOT NULL,
   [CustomerID] NChar(5),
   [EmployeeID] Int,
   [OrderDate] DateTime,
   [RequiredDate] DateTime,
   [ShippedDate] DateTime,
   [ShipVia] Int,
   [Freight] Money,
   [ShipName] NVarChar(40),
   [ShipAddress] NVarChar(60),
   [ShipCity] NVarChar(15),
   [ShipRegion] NVarChar(15),
   [ShipPostalCode] NVarChar(10),
   [ShipCountry] NVarChar(15))

  insert into @dbo_Orders
  select  c.[OrderID], c.[CustomerID], c.[EmployeeID], c.[OrderDate], c.[RequiredDate], c.[ShippedDate], c.[ShipVia], c.[Freight], c.[ShipName], c.[ShipAddress], c.[ShipCity], c.[ShipRegion], c.[ShipPostalCode], c.[ShipCountry] from dbo.Orders as c
  join (select distinct OrderId from @dbo_OrderDetails) as p on  c.OrderId = p.OrderId 

  select * from @dbo_Orders

  -- Shipper 
  declare @dbo_Shippers TABLE (
   [ShipperID] Int NOT NULL,
   [CompanyName] NVarChar(40) NOT NULL,
   [Phone] NVarChar(24))

  insert into @dbo_Shippers
  select  c.[ShipperID], c.[CompanyName], c.[Phone] from dbo.Shippers as c
  join (select distinct ShipVia from @dbo_Orders) as p on  c.ShipperId = p.ShipVia 

  select * from @dbo_Shippers

  -- Customer 
  declare @dbo_Customers TABLE (
   [CustomerID] NChar(5) NOT NULL,
   [CompanyName] NVarChar(40) NOT NULL,
   [ContactName] NVarChar(30),
   [ContactTitle] NVarChar(30),
   [Address] NVarChar(60),
   [City] NVarChar(15),
   [Region] NVarChar(15),
   [PostalCode] NVarChar(10),
   [Country] NVarChar(15),
   [Phone] NVarChar(24),
   [Fax] NVarChar(24))

  insert into @dbo_Customers
  select  c.[CustomerID], c.[CompanyName], c.[ContactName], c.[ContactTitle], c.[Address], c.[City], c.[Region], c.[PostalCode], c.[Country], c.[Phone], c.[Fax] from dbo.Customers as c
  join (select distinct CustomerId from @dbo_Orders) as p on  c.CustomerId = p.CustomerId 

  select * from @dbo_Customers

And here’s the LINQ to SQL code that downloads the data and connects the hierarchy;

using System.Collections.Generic;
using System.Data.Linq;
using System.Data.Linq.Mapping;
using System.Linq;
using System.Reflection;

namespace ConsoleApplication1
    public partial class NorthwindDataContext
        public List<Supplier> GetSuppliers()
            return AssembleHierarchy(InternalGetSuppliers());

        private static List<T> ResultOrEmptyList<T>(IMultipleResults multipleResults)
            where T : class
            IEnumerable<T> result = multipleResults.GetResult<T>();
            if (result == null)
                return new List<T>();

            return result.ToList();

        private static List<Supplier> AssembleHierarchy(IMultipleResults multipleResults)

            List<Supplier> suppliers = ResultOrEmptyList<Supplier>(multipleResults);
            List<Product> products = ResultOrEmptyList<Product>(multipleResults);
            List<Category> categories = ResultOrEmptyList<Category>(multipleResults);
            List<Order_Detail> orderDetails = ResultOrEmptyList<Order_Detail>(multipleResults);
            List<Order> orders = ResultOrEmptyList<Order>(multipleResults);
            List<Shipper> shippers = ResultOrEmptyList<Shipper>(multipleResults);
            List<Customer> customers = ResultOrEmptyList<Customer>(multipleResults);

                supplier => supplier.Products.SetSource(products.Where(product => product.SupplierID == supplier.SupplierID)));

                product =>
                    product.Category = categories.Single(category => category.CategoryID == product.CategoryID);
                        orderDetails.Where(detail => detail.ProductID == product.ProductID));

                detail =>
                    detail.Order = orders.Single(order => order.OrderID == detail.OrderID);

                order =>
                    order.Shipper = shippers.Single(shipper => shipper.ShipperID == order.ShipVia);
                    order.Customer = customers.Single(customer => customer.CustomerID == order.CustomerID);

            return suppliers;

        [Function(Name = "dbo.GetSuppliers")]
        private IMultipleResults InternalGetSuppliers()
            IExecuteResult result = ExecuteMethodCall(this, (MethodInfo)MethodBase.GetCurrentMethod());
            return (IMultipleResults)result.ReturnValue;

About mfagerlund
Writes code in my sleep - and sometimes it even compiles!

3 Responses to LINQ to SQL Performance Optimizing revisited

  1. Pingback: LINQ to SQL Performance Optimizing « Mattias Fagerlund's Coding Blog

  2. Pingback: LINQ to SQL Performance Optimization, Re-Revisited « Mattias Fagerlund's Coding Blog

  3. Nickywan says:

    This is a great solution .
    The T-SQL in the stored can be improved in order to be more efficient if you have the knowledge to do it.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: