Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Geek Lectures - Things geeks should know about » Blog Archive » Performance Quiz #13 — Linq to SQL compiled query cost — solution

Mon, 14 Jan 2008 21:08:51 GMT

PingBack from http://geeklectures.info/2008/01/14/performance-quiz-13-linq-to-sql-compiled-query-cost-solution/

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Peter Ritchie — Mon, 14 Jan 2008 21:34:50 GMT

I, for one, would be interested in seeing the code associated with this. -- Thanks

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Peter Ritchie — Mon, 14 Jan 2008 21:37:15 GMT

I think the results that others have posted in the previous post and their differences shows that it depends on the query, the database and how you implement it. Whether or not to use (or not use) a compiled query should be based on observed metrics.

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

ricom — Mon, 14 Jan 2008 21:45:23 GMT

It's just two nested loops around the queries I gave in the problem statement. The timer goes around the whole thing. Couldn't be any dumber.

start timer

for (j=0;j<batches;j++)

compile query

for (i=0;i<runs;i++)

create new data context

run query in new context

stop timer

print number of selects and time etc.

batches*runs always = 5000

In the uncompiled case it really doesn't matter how many in each batch because the same code runs the same number of times plus or minus some loop overhead.

start timer

for (j=0;j<batches;j++)

for (i=0;i<runs;i++)

create new data context

run query in new context

stop timer

print number of selects and time etc.

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

ricom — Mon, 14 Jan 2008 21:54:44 GMT

Peter writes: "I think the results that others have posted in the previous post and their differences shows that it depends on the query, the database and how you implement it. Whether or not to use (or not use) a compiled query should be based on observed metrics."

Well you'll never get me to disagree with "you should make your choice based on observed metrics."

I think what you'll find is that you break even very easily so you'll end up making your choice on the basis of:

1) is there any re-use at all

2) how much uglier is the compiled code pattern and is the benefit worth the hassle

The more complicated the query, the greater the benefits of not having to re-analyze it.

On the other hand, slower connections and/or more data processing will tend to make all the Linq overheads smaller by proportion, to the point where it could all be moot.

But the point here is to illustrate that, unlike other compilation methods, this one has very modest overhead because the compiled path is nearly the same as the uncompiled path. There isn't a seperate "compilation" vs. "interpretation" process like in say regular expressions.

Or, said yet another way, you actually always compile the query. The only question is, do you save the results of compilation or not?

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Brian — Mon, 14 Jan 2008 22:18:07 GMT

Umm, what does the "In fact, the magic number for this particular query is about 1.5 average uses to break even." tell me? Isn't that discrete, you can either run it once or twice. How do I run this query 1.5 times? The 1.5 average is pretty useless in this case.

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Paul — Tue, 15 Jan 2008 00:09:13 GMT

It tells you that when your code is executed, if the average number of times a particular query is run is >1.5, then you should consider using compiled queries.

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Peter Ritchie — Tue, 15 Jan 2008 00:16:35 GMT

RE: "...break even very easily..."

This has to do with (I'm assuming) having to change the code from being used uncompiled to compiled (and/or vice versa)...

Unfortunately the complexity of anonymous types and the way LINQ is compiled into IL makes it really hard to separate the query into it's own concern. I.e. it's hard, and not as efficient as compiled directly, to get a LINQ query into a delegate that you could then use either to compile/execute or just execute. So, it's hard to push whether a LINQ statement is compiled or not into a policy that is chosen at runtime.

Ideally you would do something like this:

MyClass myClass = new MyClass(new CompileLinqStrategy());

//...

// somewhere in MyClass:

Expression<Func<DataContext, SomeType>> expression = (NorthwindDataContext nw) => (from ...);

var results = linqStrategy.Invoke(expression);

foreach(var result in results)

{

// do something with result

}

CompiledLinqStrategy and UncompiledLinqStrategy would simply execute the expression (CompiledLinqStrategy would compile it first, of course).

Whether or not the expression is compiled or not has been completely separated from the code that uses the LINQ statement and therefore the LINQ statement need not change to switch from uncompiled to compiled and the problem of the code breaking due to this change is eliminated.

Unfortunately the C# compiler compiles a LINQ statement into much more efficient IL code than you could write manually to execute the expression, so my guess is you'd end up eating most of the performance gains.

...we need a memberof (and infoof) in C#...

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Chabster — Tue, 15 Jan 2008 02:21:13 GMT

> the main purpose of that compiled query object is to have an object, of the correct type, that also has the correct lifetime

If there was some mechanism to reference anonymous types like this:

Expression<Func<?>> expr = from o in nw.Orders select new { OrderID = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate };

there would be no need for compilation you think?

And what is the difference between CompiledQuery.Compile and the following:

public static Func<TArg0, TResult> MyCompile<TArg0, TResult>(Expression<Func<TArg0, TResult>> query) where TArg0 : DataContext {

return(query.Compile());

}

var myQuery = MyCompile((Northwind nwDb) => (from o in nwDb.Orders select new { OrderID_C = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }));

I suppose where TArg0 : DataContext restriction might be very important! It allows to introspect TArg0 and emit direct code to manipulate it!

LINQ query versus compiled LINQ query

Blog — Tue, 15 Jan 2008 03:55:11 GMT

LINQ query versus compiled LINQ query

Pawel Pabich's blog — Tue, 15 Jan 2008 03:58:45 GMT

LINQ query versus compiled LINQ query

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

Paul — Tue, 15 Jan 2008 05:54:51 GMT

>Any Linq to SQL user can choose how much or how little caching is done. They control the lifetime, they can choose an easy mechanism (e.g. stuff it in a static variable forever) or a complicated recycling method depending on their needs.

Rico, can you give us an example of how to control the lifetime of this particular compiled query? The resulting compiled query is an anonymous type. This generally limits its usage to the scope of the method that created it. It seems that the caveat is that there's a lot less flexibility in controlling the lifetime of a compiled query based on anonymous types.

re: Performance Quiz #13 -- Linq to SQL compiled query cost -- solution

ricom — Tue, 15 Jan 2008 19:39:58 GMT

You have to use the more formal syntax and store the thing in a static or something like that. Or store it as a member of some other helper type with suitable lifetime.

In this case I was able to use the simpler 'var' syntax because I had a synthetic lifetime. Normally you don't get such a luxury.

I think you could stuff it in a local, using var, then use intellisense to see the exact type, and make something of that type for instance.

re: Performance Quiz #13 -- Linq to IQueryable performance

michael.plavnik — Fri, 08 Feb 2008 07:50:25 GMT

Rico, I was itereseted in performance of compiler generated code against IQueryable<>. So I wrote simplistic provider that creates new queryable every time. Query buildup (no query translation) below takes about 70 microseconds on my Intel Core2 6600@2.4GHz machine. It is roughly equivalent to the time it takes to query first 10 orders from Nortwind database on the same machine with SqlDataReader.

var q = from a in source

group a by a[0] into g where g.Key == 'a'

from x in g select x;

10,000 queries top per processor would seem like very small number for high end data solutions, isn't it?! There is also an issue of holding transaction open for longer then necessary, which then need to be carefuly avoided in code.

Better support for compiled queries looks like a must have.

10 Tips to Improve your LINQ to SQL Application Performance

History Viewer — Mon, 05 May 2008 05:17:08 GMT

10 Tips to Improve your LINQ to SQL Application Performance

Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5 — Mon, 27 Oct 2008 22:25:31 GMT

If you are using Linq to SQL, instead of writing regular Linq Queries, you should be using Compiled Queries

Solving common problems with Compiled Queries in Linq to Sql for high demand ASP.NET websites

Omar AL Zabir blog on ASP.NET Ajax and .NET 3.5 — Mon, 27 Oct 2008 22:32:15 GMT

If you are using Linq to SQL, instead of writing regular Linq Queries, you should be using Compiled Queries

10 Tips to Improve your LINQ to SQL Application Performance

吴明浩 — Tue, 11 Nov 2008 08:07:17 GMT

Heythere,backagain.InmyfirstpostaboutLINQItriedtoprovideabrief(okay,bitdetailed)in...