Performance Quiz #13 -- Linq to SQL compiled queries cost

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

Fri, 11 Jan 2008 23:28:17 GMT

PingBack from http://geeklectures.info/2008/01/11/performance-quiz-13-linq-to-sql-compiled-queries-cost/

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

Fri, 11 Jan 2008 23:28:31 GMT

PingBack from http://geeklectures.info/2008/01/11/performance-quiz-13-linq-to-sql-compiled-queries-cost/

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

Anonymouse — Sat, 12 Jan 2008 00:28:21 GMT

Only once since the query must be "compiled" anyway.

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

Grant — Sat, 12 Jan 2008 01:22:21 GMT

[Only once since the query must be "compiled" anyway.]

I doubt that.

There is almost certainly additional overhead in finding out if the query has already been compiled (using a hashcode or similar mechanism?) and allocating storage for the compiled query in some sort of collection of compiled queries.

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

JamesNT — Sat, 12 Jan 2008 01:36:50 GMT

I doubt it as well. Unfortunately, I do not know enough about Linq to give an informed answer. Therefore, I will be watching Rico's blog with interest as he posts the answer.

JamesNT

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

Oleg Mihailik — Sat, 12 Jan 2008 03:29:06 GMT

There is no implicit caching of compiled queries, so I vote for 'once' as well.

That's the problem: LINQ doesn't remember the expressions when they are executed twice or more. It always re-interprets an expression tree from scratch.

Otherwise, we wouldn't need CompiledQuery functionality in the first place.

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

Peter Ritchie — Sat, 12 Jan 2008 18:23:48 GMT

It depends on the query. Taking into account the cost of the compile, I've had some compiled queries take as many as 15 executions before they paid for themself.

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

Galcho — Sun, 13 Jan 2008 01:32:25 GMT

I think we need to use the following formula to find number fo execution where time is same for both cases

X.Ec + Tc = X.Er

X = Tc /(Er - Ec)

where

X - number of executions

Ec - Execution of compiled query

Tc - time needed for compilation

Er - execution of raw query

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

omario — Mon, 14 Jan 2008 09:22:33 GMT

I think queries are cached for the lifetime of thedatacontext.

And it worth compiling query if you use it with different datacontexts more than once.

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

Alexey Lavnikov — Mon, 14 Jan 2008 11:22:57 GMT

I doubt that queries are cached in DataContext.

I think if you use same query more than once, compiled queries are better.

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

JamesCurran — Mon, 14 Jan 2008 19:23:45 GMT

Well, from my testing (using the query Rico gave), the pay-off is immediate.

I'm going to assume the the Comments editor is going to mangle the chart below, so to summarize, Compiled *always* wins, even for just one repitition. At higher repetitions, the ratio settles at 5:1, but oddly, at low reps, compiled win by as much as 25:1. (Some -- but not all -- of that can be attributed to the timer resolution)

Reps uncompiled Compiled ratio

1 :00.25 :00.014 18:1

5 :00.37 :00.015 25:1

10 :00.27 :00.02 14:1

50 :00.81 :00.06 13:1

500 :01.98 :00.38 >5:1

5000 :14.86 :03.10 <5:1

50000 2:26 :27 >5:1

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

Peter Ritchie — Mon, 14 Jan 2008 19:34:47 GMT

James, your times don't seem to detail the compilation time? My tests show that 1 repetition uncompiled is slower than compiling and 1 repetition of the compiled object.

Although, I haven't tested specifically with Rico's example, in my tests the compilation time begins to be offset at 15 repetitions. As I stated earlier, this offset is likely dependant on database and the query involved.

Rico teases us with a LINQ to SQL performance quiz

The Wayward WebLog — Mon, 14 Jan 2008 19:46:26 GMT

Check it out and see if you know the answer. Rico's Performance Tidbits

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

JamesCurran — Mon, 14 Jan 2008 19:53:21 GMT

Here is my code for the compiled case. The time is for one call to this method. ("reps" is a hard-code static const which I manually change between runs.) ("total" is used to prevent the compiler from eliding unused code, and to compare with the results from Test1() which was the uncompiled version.)

static int Test2()

{

NorthwindDataContext nwdc = new NorthwindDataContext("(local)");

var fq = CompiledQuery.Compile((NorthwindDataContext nw) =>

(from o in nw.Orders

select new

{

OrderID = o.OrderID,

CustomerID = o.CustomerID,

EmployeeID = o.EmployeeID,

ShippedDate = o.ShippedDate

}).Take(5)

);

int total = 0;

for (int i = 0; i < reps; ++i)

{

var t= fq.Invoke(nwdc);

foreach (var a in t)

{

total += a.OrderID;

}

return total;

}

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

ricom — Mon, 14 Jan 2008 21:29:13 GMT

James: Things you want to control for at low reps:

-jitting time (run once and throw away that result to get all the code jitted)

-state of the connection pool (you don't want to charge the cost of making the connection to one algorithm, and not the other so make several connections first and discard those results too)

What I did was I ran several thousand batches first of both cases, discarded those results, and then measured. Also I tried them in different orders to make sure that the later runs were not penalized by say more things on the GC heap (they were not as it turns out but I controlled for it anyway)

I don't understand how the compiled query can possibily win in the single use case :)

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

Peter Ritchie — Mon, 14 Jan 2008 21:32:06 GMT

If I include the cost of the compilation in the times, I get ratios like this with that sample code:

uncompiled compiled

1 00:00:00.1047737 00:00:00.0903456 1.2:1

5 00:00:00.0921325 00:00:00.1044020 0.8:1

10 00:00:00.0982715 00:00:00.1024622 1,0:1

50 00:00:00.1724429 00:00:00.1112768 1.5:1

500 00:00:00.6105094 00:00:00.2092043 2.9:1

5000 00:00:05.0588695 00:00:01.2370046 4.1:1

50000 00:00:49.9114562 00:00:11.3373295 4.4:1

Now, with a different query on the same northwind database the results are different again:

uncompiled compiled

1 00:00:00.4928271 00:00:00.3322023 1.5:1

5 00:00:01.1390319 00:00:01.1036043 1.0:1

10 00:00:02.1391678 00:00:02.0752555 1.0:1

50 00:00:10.0530105 00:00:09.6794834 1.0:1

500 00:01:41.2241573 00:01:35.6616519 1.1:1

And the copde/compiled-query is:

static int Test()

{

NorthwindDataContext nwdc = new NorthwindDataContext(@"(local)\SQLExpress");

int total = 0;

Stopwatch stopwatch = new Stopwatch();

stopwatch.Start();

var cq = CompiledQuery.Compile((NorthwindDataContext nw) =>

(from customer in nw.Customers

select new

{

Customer = customer,

Orders = from o in nw.Orders

join co in nw.Orders

on o.CustomerID equals co.CustomerID

where co.CustomerID == customer.CustomerID

select o

}));

for (int i = 0; i < reps; ++i)

{

var q = cq.Invoke(nwdc);

foreach (var a in q)

{

total += a.Orders.Count();

}

stopwatch.Stop();

Trace.WriteLine(String.Format("{0} {1} ({2} ticks)", reps, stopwatch.Elapsed, stopwatch.ElapsedTicks));

return total;

}

A similar query on a different database produced results that showed compiled was slower than uncompiled up to about 15 reps.

So, it really depends on the query and the database.

As with any optimizations, it should be checked to make sure it really is an optimization and that it's worth doing...

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

ricom — Mon, 14 Jan 2008 22:09:47 GMT

Peter correctly points out that if you're bringing back lots of data (as in his second example) all this query compilation stuff just so much noise. The real cost is in processing the data and creating the objects.

With that much data coming back, compilation isn't hurting but it sure isn't helping much. Pretty much you've got the raw time to read the data and create the objects and that's it. Which is as it should be :)

I'm guessing now but I think the primary difference between my results and Peter's (where we were using the same query) is that I put in a dummy run to eliminate the cost of jitting and the cost of getting the database connection pool set up from the experiment. Also I keep the total work constant so that I never have to measure any small times.

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

Anonymouse — Mon, 14 Jan 2008 23:30:35 GMT

I was right, but in some sense I cheated. :P

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

Peter Ritchie — Mon, 14 Jan 2008 23:42:19 GMT

Yes, I didn't perform and dummy runs to eliminate jitting in the metrics. Unless I'm NGENing a product I like to see "worst" case. If the user runs the application for the first time and it is prohibitively slow, they're not going to want to use it again :-). But, metrics not including JIT times are equally as informative.

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

Chabster — Tue, 15 Jan 2008 01:53:13 GMT

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Linq;

using System.Diagnostics;

using System.Linq.Expressions;

namespace LINQPerf

{

class Program

{

static void Main(string[] args) {

Stopwatch s, sAvg;

const int iterations = 10;

const int ordersToFetch = 10000;

Northwind nw;

nw = new Northwind("Data Source=D:\\Development\\Projects\\C#\\LINQPerf\\LINQPerf\\Northwind.sdf");

//nw.Log = Console.Out;

nw.ObjectTrackingEnabled = false;

//s = Stopwatch.StartNew();

//var cacheQuery = from orders in nw.Orders select orders;

//cacheQuery.Take(ordersToFetch).GetEnumerator();

//Console.WriteLine("Cache: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled: " + s.ElapsedMilliseconds);

Console.WriteLine();

sAvg = Stopwatch.StartNew();

{

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_0 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 0: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_1 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 1: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_2 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 2: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_3 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 3: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_4 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 4: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_5 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 5: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_6 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 6: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_7 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 7: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_8 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 8: " + s.ElapsedMilliseconds);

s = Stopwatch.StartNew();

(from o in nw.Orders select new { OrderID_N_9 = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

Console.WriteLine("Non compiled 9: " + s.ElapsedMilliseconds);

}

Console.WriteLine("Non compiled avg not in loop: " + ((double)sAvg.ElapsedMilliseconds / 10));

s = Stopwatch.StartNew();

for (int i = 0; i < iterations; i++) {

(from o in nw.Orders select new { OrderID_N_AVG = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch).GetEnumerator();

}

Console.WriteLine("Non compiled avg in loop: " + ((double)s.ElapsedMilliseconds / iterations));

Console.WriteLine();

//s = Stopwatch.StartNew();

var compiledQuery = CompiledQuery.Compile((Northwind nwDb) => (from o in nwDb.Orders select new { OrderID_C = o.OrderID, CustomerID = o.CustomerID, EmployeeID = o.EmployeeID, ShippedDate = o.ShippedDate }).Take(ordersToFetch));

//Console.WriteLine("Compile: " + s.ElapsedMilliseconds);

//Console.WriteLine();

s = Stopwatch.StartNew();