I work in an environment where security is important. We implement this using stored procedures to provide an API to the data - so no direct access to tables or views. This shifts some work into SQL but ultimately means that we have fairly simple code and minimal work when we decide to change the DB structure. It also means we don’t really touch linq/sql - which I suspect might be a good thing.
@ Michael Hamrah : “That’s the Coding Horror I remember and miss!” Me too… a lot of silence and lighter posts lately on this blog.
@ John Wards : “Dam it. I want my money back. I thought this was a baiting post about PHP.” LOLLLL :)))))
@ Pop Catalin : “P.S. This is another missuse of the work “scalable”” Yes, you are so right about that. Performance and scalability, although closely in hand, don’t mean the same thing. The way I see it scalability means the ability of a system to increase its throughput by “throwing” more machines at it.
Bravo! Great article. I will tell my team to get their stopwatches ready. I wonder how much performance is still hidden in our applications.
@Jonathan Evans,
You are probably aware of this, but I thought I would mention it in case you are not. LINQ has fairly decent support for stored procs. I have no idea what the specifics of your environment are, but you might be able to get some of the benefits of LINQ while still maintaining the security and performance you are seeing with your stored procs.
Jeff is back!!!
Great post. Right on the money.
I read the MSDN article this month on compiled queries and I knew there was something that didn’t quite taste right. You have spotted it and put some salt on it. Good job and thanks!
Wouldn’t it be awesome if C# had macros and you could write a quick macro to do the conversion from the Linq to the ugly version of the code, and then use that macro when the performance optimization did matter.
Sadly, I’m not even entirely sure whether C# has macros of this nature or not, because I’m trapped in v2.0 of the .NET framework. Woe is me.
@ Joshua : “Sadly, I’m not even entirely sure whether C# has macros of this nature or not, because I’m trapped in v2.0 of the .NET framework. Woe is me”
You can very well define an #if DEBUG … #endif region
Also, there are certain source code processing tools out there…
Maybe I’m just not awake yet, but how is this:
db.PostTags.Where(t => t.PostId == this.Id).ToList();
generating 500k queries? It’s 1 query returning 500k results…
@Andrei Rinea - C# preprocessor directives are a long way off from real macros like lisps have:
http://www.apl.jhu.edu/~hall/Lisp-Notes/Macros.html
And sure you could use some sort of preprocessor, or aop or whatever, but any of those solutions would be a hack compared to having language support for macros.
You are putting the compiled query syntax in a bad light… It would be a better example if you take advantage of type inference:
var q = CompiledQuery.Compile(
(Northwinds nw, int orderid) =>
from o in nw.Orders
where o.OrderId == orderid
select o);
Or if it’s a class field declaration:
Func<Northwinds, IQueryable, int> q = CompiledQuery.Compile(
(nw, orderid) =>
from o in nw.Orders
where o.OrderId == orderid
select o);
Hum, I guess I should have previewed my comment …
Func<Northwinds, IQueryable<Orders>, int> q = CompiledQuery.Compile(
(nw, orderid) =>
from o in nw.Orders
where o.OrderId == orderid
select o);
I agree that Compiled Queries aren’t that bad and their benefits out way any sort of negative feelings of their aesthetics. I usually write the query first, make sure it works in LinqPad or in the app then convert to Compiled Query when I’m ready.
As a note to gain the benefits of compiling your query you really need to make it static or you are actually making things worse since the compilation adds overhead and it would happen on every request.
@JeremyBoyd “shaving off 5 ms for an function call isn’t going to do anyone any good, because over 1000 request, thats only 50 seconds.”
Actually that’s only 5 seconds.
I like Linq-to-SQL. I think it makes a lot of things a lot easier.
However, because I’m a speed freak I’ve adopted a slightly different strategy. Plainly, I like to use Linq-to-SQL for all of the one-record CRUD statements. I’m talking things like updating the user’s name, selecting their email address, adding a new user, etc. These statements are all very simple even on the SQL side of things, but writing them in Linq makes them even easier. And in fact these type of statements typically account for 90% of the queries from my sites.
The other 10% of queries are more involved with complex joins, sometimes utilizing full-text searching and they act on a much larger set of data. So with those queries I go all out. I’ll write an efficient stored proc so that SQL server can cache those query plans aggressively and get the last bit of performance out of them. And then I’ll call the stored proc from ADO.NET with the most performant option, the SqlDataReader. It’s a little more code to do all of this. But to me it’s worth it.
Nice one Jeff. Sorry to lead you astray.
If you notice near the middle of my article I did mention that the reason this even was an issue was because I was doing orders of magnitude more queries than I should have been. I just found the whole situation quite interesting, in that the query compile overhead took more time than the SQL database query (the DB was running locally with the web server). For that low usage application it wasn’t worth the time to do the macro optimization and the micro was fine. Sometimes crafting a better algorithm isn’t worth the time. 
I now use Linq to SQL very heavily in online games with millions of users, partitioned databases, and tens of thousands of simultaneous requests. Very few queries are compiled due to well thought out algorithms. Now our biggest CPU bottleneck is Linq to SQL’s dynamic update system that uses reflection, so we use SPROCs for critical areas. But, all-in-all, the ROI in Linq to SQL is great.
Oracle and Sybase ASE do cache SQL statements and execution plans that differ in literal values, even if you don’t parameterize them. SQL server doesn’t. This isn’t Linq-to-SQL’s fault, but rather SQL server’s fault.
Nice article Jeff and something to get our teeth into. I have a data aggregator and roll-up application that’s been behaving badly. It uses Linq to SQL heavily (the queries themselves aren’t hellishly complex, they just operate on single tables and there’s no joins) and I’ve been meaning to get time to profile it (code and SQL) properly. This article has given me the boot up the backside to go do something about it.
More of this kind of thing please!
Minor nitpick:
“[T]housands of hits per hour” is one or just a few hits per second. That sounds like low traffic to me.
(Raise the number by a couple of orders of magnitude and the discussion makes much more sense. Let’s assume that.)
Jeff -
“Stack Overflow has dozens to hundreds of plain vanilla uncompiled Linq to SQL queries on every page.”
Could you elaborate on this a bit? Curious to know (the db accesses required to construct a page) what’s requiring double to triple-digit numbers of queries on a page?
Thanks