Compiled LINQ queries appear to be slower than non-Compiled

Question

I was recently asked to look into compiled linq queries for their performance benefits.  So, i looked at our current web services app which has a nicely isolated data layer which would make it a do-able task to actually convert it.  However, before I started this somewhat time consuming process I wanted to be sure it would be worth the effort, so I built a small test scaffold.

I have a table that in our test enrionment only has 3800 rows.  Each data row is around 800 bytes.  So, i thought of a query that our web site uses, and that is to page through the data 500 rows at a time.  Currently we use Take and Skip to do the paging, but for this I just implemented Take.

To my surprise, calling this method more than 6-7 times actually causes it to SLOW down when compiled.  That is not what I expected, but attached is the code that I ran that shows my results.  The goal in this exercise was not to write an efficient query, but rather to take the same query and run it in both environments to see if there is indeed a performance gain.  I will confess that originally, I was only returning the COUNT of the rows and there the compiled query blew away the non-compiled, but since that is an artificial test (we really want to see DATA, not COUNTS), I changed it to retrieve the data.

Any thoughts?

Code follows:

using System;
using System.Collections.Generic;
using System.Data.Linq;
using System.Diagnostics;
using System.Linq;
using TC.Data.CollectOnTime; // our database class - this contains the Linq2SQL definitions and data context


namespace TestIT
{
  class Program
  {
    const int LIMIT = 8;  // 7 seems to be the threshold. Less than 7 is faster, more than 7 is slower
    static string connect = @"server=.\sqlexpress64;initial catalog=CollectOnTime; integrated security=true";       

    //
    // The table we are selecting from only has 3800 rows. The column we are using for this test is NOT an index
    // The ~ RowSize for each row is 800 bytes
    // The purpose was not to build an optimum query, but rather to see if a compiled query is faster or slower than a standard query
    // The conclusion is for single shots, it is faster, but when calling the same query multiple times, it is quite a bit slower
    // which is contrary to what I have been seeing in some research.
    //

    static void Main(string[] args)
    {

      Stopwatch swStandardLinq = new Stopwatch();
      Stopwatch swCompiledLinq = new Stopwatch();
      //
      // Create an object to contain the query arguments
      //
      SelectArguments selectArgs = new SelectArguments() { companyId = 10, takeCount = 500};
      //
      // Run a linq query to retrieve 500 rows from the DB
      //
      swStandardLinq.Start();
      for (int i = 0; i < LIMIT; i++)
      {
        CallStandardLinqQuery(selectArgs);
      }
      swStandardLinq.Stop();
      //
      // Run the same query compiled
      //
      swCompiledLinq.Start();
      for (int i = 0; i < LIMIT; i++)
      {
        CallCompiledLinqQuery(selectArgs);
      }
      swCompiledLinq.Stop();
            
      Console.WriteLine("Elapsed Time For {0} Standard LINQ Query: {1}", LIMIT, swStandardLinq.Elapsed);
      Console.WriteLine("Elapsed Time For {0} Compiled LINQ Query: {1}", LIMIT, swCompiledLinq.Elapsed);

      Console.WriteLine(string.Format("Ticks Difference (Standarg - Compiled): {0}", (decimal)(swStandardLinq.ElapsedTicks - swCompiledLinq.ElapsedTicks)));

      Console.WriteLine(string.Format("% Difference: {0:#.000000}", (decimal)(swStandardLinq.ElapsedTicks - swCompiledLinq.ElapsedTicks) / swStandardLinq.ElapsedTicks));

      Console.ReadLine();
    }

    /// <summary>
    /// The basic types for the function are DataContext, InputArguments, and Output
    /// </summary>
    public static readonly Func<CollectOnTimeDataContext, SelectArguments, IQueryable<PaymentRule>> ruleListQuery =
                CompiledQuery.Compile((CollectOnTimeDataContext db, SelectArguments selectArgs) =>
                (from r in db.PaymentRules
                 where r.MerchantId <= selectArgs.companyId
                 select r));
    /// <summary>
    /// Simulates our DAL layer where our web services call into to get data
    /// </summary>
    /// <param name="selectArgs">Arguments for the Query</param>
    private static void CallCompiledLinqQuery(SelectArguments selectArgs)
    {
      List<PaymentRule> ruleList;
      using (CollectOnTimeDataContext db = new CollectOnTimeDataContext(connect))
      {
        ruleList = ruleListQuery(db, selectArgs).Take(selectArgs.takeCount).ToList();
      }
    }
    /// <summary>
    /// Simulates our DAL layer where our web services call into to get data
    /// </summary>
    /// <param name="selectArgs">Arguments for the Query</param>
    private static void CallStandardLinqQuery(SelectArguments selectArgs)
    {
      List<PaymentRule> ruleList;
      using (CollectOnTimeDataContext db = new CollectOnTimeDataContext(connect))
      {
        ruleList = (from r in db.PaymentRules
              where r.MerchantId <= selectArgs.companyId
              select r).Take(selectArgs.takeCount).ToList();
      }
    }
  }
  /// <summary>
  /// Class to hold any arguments for the Query
  /// </summary>
  public class SelectArguments
  {
    public int companyId { get; set; }
    public int takeCount { get; set; }
  }

}

 

Answer 1

Hello twreynol,

 

Thanks for your question.

As your description, I think Jon Skeet's explain about this in StackOverFlow is perfect: ...if you've got a common predicate, then you can filter your list by that to start with, to come up with a new IEnumerable<T>. The predicate will be applied lazily, so it won't make any difference to execution speed, but it might make your code more readable. It could slow things down very slightly as it will introduce an extra level of indirection when you've effectively got two different where clauses.

If the result of applying the filters will have very few results, you may want to materialize it (e.g. by calling ToList) and remember the result - that way you don't need to query the whole thing again for the second query.

However, the big benefit that I can see would be from only calling Single once for each query. Currently you're executing the whole query for every single property - that's clearly inefficient.

And another, I think "Take" and "Skip" are the same with "Single". So please see this: http://stackoverflow.com/questions/2494475/linq-query-performance-comparing-compiled-query-vs-non-compiled

I hope it can help you. If you have any questions please feel free to let us know.

 

Have a nice day,

---

Jackie Sun [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thanks for your reply, but this was not about optimizing a specific query, rather it was to simulate multiple folks hitting our web site to get the "list" of data items to show.  My simulation is multiple folks (LIMIT=8) asking for more or less a list of 500 rows to page through on our web site.  The query is not significant, what I was trying to check was whether or not pre-compiling the query would (in the bigger picture) speed it up or slow it down, and my conclusion from this experiment is that it will slow it down.  My Grid Control (DevX) works with an ObjectDataSource which pages the data using the Take and Skip capability.

I thought that this was a fairly realistic simulation of a web site getting paged data for multiple users.  That is what I am trying to guage performance for.

Answer 3

 

"twreynol_hotmail.com" wrote in message news:298ad21e-fbc6-43b5-9d90-f76b541c575b...

Thanks for your reply, but this was not about optimizing a specific query, rather it was to simulate multiple folks hitting our web site to get the "list" of data items to show.  My simulation is multiple folks (LIMIT=8) asking for more or less a list of 500 rows to page through on our web site.  The query is not significant, what I was trying to check was whether or not pre-compiling the query would (in the bigger picture) speed it up or slow it down, and my conclusion from this experiment is that it will slow it down.  My Grid Control (DevX) works with an ObjectDataSource which pages the data using the Take and Skip capability.

I thought that this was a fairly realistic simulation of a web site getting paged data for multiple users.  That is what I am trying to guage performance for.

 

my response ------------------------------------------------------------------

This is not correct. A complied query is going to take a speed hit the first time it's used. The query is cached in static memory, meaning as long as the ASP.NET worker process is running, the query is cached in static memory and subsequent calls to the query are going to use the cached query in memory, which is faster than a non-complied query.

Each time you make the call to the query,  and it is not a compiled query, the query has to be compiled again, slowing the query down.

The other thing that will slow a query down is not using a complied view. Of course, this applies to the ADO.NET Entity Framework and not Linq-2-SQL, because I don't know what Linq-2-SQL is doing. 

 http://msdn.microsoft.com/en-us/library/cc853327.aspx

http://msdn.microsoft.com/en-us/library/bb896240.aspx

If you are using Linq-2-SQL, then it might have something similar.

 

Answer 4

I agree that it SHOULD be just the opposite of my results, but my testing showed otherwise.  That is why I am a little confused and why we will not be converting our queries anytime soon. (we use Linq2Sql not the Entity Framework because our app was built prior to Entity Framework being ready for consumption).

Here are some actual results from running the code shown above (all on a single machine, so the envrionment is static):

First run - 10 iterations - throw this away so that everything that should be cached on the DB is cached.

Second Run - 1 Iteration StandardQuery 00:00:00.1535658 CompiledQuery 00:00:00.0467466

Third Run - 5 Iterations StandardQuery 00:00:00.2280352 CompiledQuery 00:00:00.1989517

Fourth Run - 10 Iterations StandardQuery 00:00:00.3135625 CompiledQuery 00:00:00.3768482

Fourth Run - 50 Iterations StandardQuery 00:00:01.0473189 CompiledQuery 00:00:01.8479672

Fifth Run - 100 Iterations StandardQuery 00:00:01.9407773 CompiledQuery 00:00:03.6676979

Fifth Run - 1000 Iterations StandardQuery 00:00:17.8275473 CompiledQuery 00:00:36.2204886

As you can see from the evidence, when we start approaching higher numbers of simulated users, the performance tanks, again the query is simple, yet simulates a web query (get 500 rows of data from a single table).

Any thoughts?

Answer 5

On 2/4/2011 10:11 AM, twreynol_hotmail.com wrote:

> I agree that it SHOULD be just the opposite of my results, but my

> testing showed otherwise. That is why I am a little confused and why we

> will not be converting our queries anytime soon. (we use Linq2Sql not

> the Entity Framework because our app was built prior to Entity Framework

> being ready for consumption).

>

> Here are some actual results from running the code shown above (all on a

> single machine, so the envrionment is static):

>

> First run - 10 iterations - throw this away so that everything that

> should be cached on the DB is cached.

>

> Second Run - 1 Iteration StandardQuery 00:00:00.1535658 CompiledQuery

> 00:00:00.0467466

>

> Third Run - 5 Iterations StandardQuery 00:00:00.2280352 CompiledQuery

> 00:00:00.1989517

>

> Fourth Run - 10 Iterations StandardQuery 00:00:00.3135625 CompiledQuery

> 00:00:00.3768482

>

> Fourth Run - 50 Iterations StandardQuery 00:00:01.0473189 CompiledQuery

> 00:00:01.8479672

>

> Fifth Run - 100 Iterations StandardQuery 00:00:01.9407773 CompiledQuery

> 00:00:03.6676979

>

> Fifth Run - 1000 Iterations StandardQuery 00:00:17.8275473 CompiledQuery

> 00:00:36.2204886

>

> As you can see from the evidence, when we start approaching higher

> numbers of simulated users, the performance tanks, again the query is

> simple, yet simulates a web query (get 500 rows of data from a single

> table).

>

> Any thoughts?

>

 

I don't know what to tell you other than possibly not using Linq-2-SQL

for enterprise level solutions, switching to another ORM. Linq-2-SQL is

for light weight stuff. Where MS is putting its efforts to improving

things is with EF not Linq-2-SQL such as cache execution plans with SQL

server and other improvements. with VS2010 you can convert your

Linq-2-SQL solutions over to EF.

 

<http://thedatafarm.com/blog/data-access/important-entity-framework-query-improvements-for-net-4-0/>

<http://blogs.msdn.com/b/wriju/archive/2010/07/09/ef4-use-mergeoption-notracking-for-better-query-performance.aspx>

<http://msdn.microsoft.com/en-us/library/bb738562.aspx>

 

 

Along with EF, there is also nHibernate. Again Linq-2-SQL I wouldn't use

in an enterprise level Web solution. Maybe you need to start testing

some other ORM(s).