Linq Compiled Queries Q & A

I did a series of postings on Linq Compiled Queries last year, I recently got some questions on those postings that I thought would be of general interest.


Why use the ‘new’ keyword in this snippet?

var q = from o in nw.Orders
select new {o.everything …};


If you did just :

var q = from o in nw.Orders
select o;

You’re getting editable orders. Linq then has to track them in case you change them and want to submit the changes. If you use new effectively you’re making a copy of the orders that is not going to be change tracked. That’s faster for read only cases. The other thing you can do is mark the query context as read-only and then you get the same effect.  When I wrote that test case, that feature wasn’t available yet so I used new to simulate it.


What do you mean when you say that linq will ‘Create custom methods that bind the data perfectly’ ?


Whenever you use linq to sql to read data from a database it has to do two important things for you. The first is convert your Linq query into SQL. The second is to make a method that takes the stream of data that comes back from the database and converts it into the managed objects you required. That’s the data-binding step. Linq creates the necessary methods automatically, and it makes the perfect code for doing this.


How did Linq to SQL beat your ADO.Net code for insert times.  Shouldn’t a tie be the best possible result?


The SQL I used in my test case was pretty much the standard simplest SQL you would use for such a job. The automatically generated SQL from Linq was better than what I wrote by hand because they had parameterized the insert statements which I never bothered to do. Had I changed my SQL to what they created it would have been a tie. This is kind of like when the C++ compiler finds a machine code pattern that is better than what you would have written doing it by hand because it did something you don’t usually bother doing with hand tuned machine code. But you *could* replace what you wrote with what the compiler generated.


What are the downsides to precompiled queries?


There is no penalty to precompiling (see Quiz #13). The only way you might lose performance is if you precompile a zillion queries and then hardly use them at all — you’d be wasting a lot of memory for no good reason. 

But measure 🙂

Comments (5)

  1. Anders Borum says:

    Compiled queries for the win! o/ 🙂

  2. Alexander Gornik says:

    Rico, about the linq performance.

    Didn’t the linq team thought about massive insert / update scenarios?

    Our experience shows that you simply can’t insert more than several thousand entites (2000-3000) in one datacaontext. Performance in that case seems to fall exponentially (i guess, due to some internal entity graph manipulation). We had to use SqlBulkCopy for that.

    And the massive update scenario isn’t just supported. Although i can easely imagine something like that:

    users.Where(u => u.Role == "Operator").Set(u => u.Status = "Locked")

  3. ricom says:

    My impression was that they only went for a core slice of the database operations.  It’s not everything but it’s definately not nothing 🙂

    Now, interestingly, I think anybody could come along and implement the .Set operator you’re talking about there.  You can use the select statement that everything before the Set would have generated and then look at the expression tree for the mapping function in the .Set, figure out which columns to change then make something like

    update foo set <whatever> where foo in (the select statement from before the select)

    The hardest part I think is figuring out how you want to express compound updates (many columns into related values).

    I’m not a Linq internals expert by any means but I did build a conceptual query engine many years ago (on top of OLEDB) and the above is how our updates worked.

  4. Alexander Gornik says:

    Thanks for the reply.

    Interesting part about the updates, although it’s definitely not that easy to implement with decent quality. 🙂

    And the insert problem is still here.

    It’s even more sad, that EF doesn’t handle this issues too, as it seems to me now.

    p.s: where foo in … can also produce pretty ineffective queries, imho, but at least we can pray for query optimizer here :).

  5. ricom says:

    Actually since about SQL2005 "where foo in (…)" has been really about the same plan quality as an inner join (it really is just another way of writing an inner join after all) in all the cases I’ve had to use it.  So that’s a good thing 🙂