DLinq (Linq to SQL) Performance (Part 2)
So after getting some high level times I started digging into the particulars of the costs more broadly and I ended up studying a very simple query like the below one.
Northwinds nw = new Northwinds(conn);
var q = from o in nw.Orders
where o.OrderId == orderid
select o;
foreach (Orders o in q)
{
…
}
It was the per-query costs that seemed to be the greatest trouble spot in the then-current profiles. Those costs would be the most problematic for complex queries which return comparatively few rows – all too common in business logic. For small numbers of rows the rough bucketization of costs looked like this:
| Category | Time |
| Total Benchmark | 100.00% |
| Query Build | 24.40% |
| Query Enumeration | 74.55% |
| Dispatch Glue | 7.34% |
| Jitting Costs | 18.07% |
| Data Reading | 49.14% |
| Misc | 1.06% |
In short the problem is that the basic Linq construction (we don’t really have to reach for a complex query to illustrate) results in repeated evaluations of the query if you ran the query more than once.
Each execution builds the expression tree, and then builds the required SQL. In many cases all that will be different from one invocation to another is a single integer filtering parameter. Furthermore, any databinding code that we must emit via lightweight reflection will have to be jitted each time the query runs. Implicit caching of these objects seems problematic because we could never know what good policy is for such a cache – only the user has the necessary knowledge.
But all is not lost... the usual parameterized query model seems to be helpful here without unduly complicating everything. You could imagine a sequence something like this:
Func<Northwinds, IQueryable<Orders>, int> q =
CompiledQuery.Compile<Northwinds, int, IQueryable<Orders>>
((Northwinds nw, int orderid) =>
from o in nw.Orders
where o.OrderId == orderid
select o );
Northwinds nw = new Northwinds(conn);
foreach (Orders o in q(nw, orderid))
{
...
}
The important thing here now is that q is a durable thing that can be applied to different data contexts and we've identified the orderid paramater. You'll have to forgive my syntax I don't think there's a compiler in existance (old or new) that compiles precisely the above but hopefully you'll get the idea.
Importantly, upon compilation, the query can be reduced to some kind of prepared statement. At this time any helper methods that need to be code-generated are also created. Upon binding we do the minimal query formatting for the constants and no jitting. The compiled query has lifetime specified by the user, so it lives exactly as long as it needs to.
These operations would drastically reduce per query overhead while simultaneously giving us a good place to hang state with suitably lifetime – compiled queries.
That seemed to get us forward progress on the per-query costs but what about the per-row costs?
We had a couple of different ideas to help with those as well.
Stay tuned for part 3. :)
P.S. Keep your eye on Matt Warren's Weblog as he'll likely comment on what I'm saying as the series evolves, it was his hand that actually made the changes I'm talking about here.
