Linq to SQL – DataLoadOptions – Does it improve performance?

Following on from my previous post on DataLoadOptions, I decided to do a quick test to see if it does actually improve performance, even with its limitations.

If we take this query, with DataLoadOptions only a single query is executed, while with it not set a query is executed per order.

DataLoadOptions options = new DataLoadOptions();
options.LoadWith(Customer => Customer.Orders);
db.LoadOptions = options;

var query = from c in db.Customers
                        select c;

            foreach (var c in query)
            {
                Console.WriteLine(“{0}, Phone: {1} with {2} orders”, c.ContactName, c.Phone, c.Orders.Count);
            }

Without using DataLoadOptions, it took 1,739,904 Ticks to execute, while with using them it took 1,487,722 Ticks. An improvement, but nothing ultra special.

If we consider this query, which if you have read the previous, causes a query to be executed for each order even with DataLoadOptions on:

DataLoadOptions options = new DataLoadOptions();
options.LoadWith(Customer => Customer.Orders); options.LoadWith(Order => Order.Order_Details);
db.LoadOptions = options;

var query = from c in db.Customers
                     select c;

         foreach (var c in query)
         {
             Console.WriteLine(“{0}, Phone: {1}”, c.ContactName, c.Phone);
             foreach (var o in c.Orders)
             {
                 Console.WriteLine(“Order Details for OrderID: {0} with {1} lines”, o.OrderID, o.Order_Details.Count);

                 foreach (var od in o.Order_Details)
                 {
                     Console.WriteLine(“Product ID: {0}”, od.ProductID);
                 }
             }
         }

Without using DataLoadOptions, it took 10,003,725 Ticks to execute.  While using DataLoadOptions, taking into account the time to setup the options, it took 6,536,320 Ticks.  Much clearer performance improvements when a larger number of queries are being executed.

There is definitely performance improvements to be had even with it not being able to load all the data in at the same time and so should be used when executing these kinds of queries.

However, what happens if we are not using the Order/Order Details information and simply just using Customer data?  Executing our first query again but without c.Orders.Count, with options set it takes 1,609,867 Ticks, without it takes 1,174,217 Ticks.  So, if your not using them, then there is additional overhead to be aware of.