While I may have mixed emotions toward LINQ to SQL, we've had great success with it on Stack Overflow. That's why I was surprised to read the following:
One of your best writings! And I do agree 100%.
Is this really a surprise? Most ORM tools will add overhead to your application, they simply aren’t capable of any real query optimization.
Aside from the anonymous types, I’m not really sure there is any benefit here at all. Personally, I think your ADO.NET ExecuteQuery() is the easiest code to read. It also ensures that your object model evolves correctly, relying on an ORM to do this for you will inevitably lead to a spotty model.
Excellent review Jeff. I am glad that Linq-To-Sql worked for you. I couldn’t even start because I didn’t get Microsoft’s Website-spark incentive, or any other incentive from them for that matter.
However I worked with mySql+ASP.Net MVC+jQuery stack and the performance seems quite wonderful even though I am crunching queries by the dozen every hour.
Stackoverflow’s success is definitely awe-inspiring. The concept is excellent. That’s why I would love to see more websites based on it out there. I am sure others will appear.
You’ve done a great job Jeff.
You use LINQ to SQL? I would have figured that your team would have wanted to move to the Entities Framework. Wouldn’t their be a small benefit by changing to the Entities framework?
Sure you don’t get the lazy load and other automated query generation things that LINQ to SQL provides, but the Entities framework is designed to mimic more of how SQL was designed.
We recently switched our product over to it and noticed some performance enhancements.
there* (sorry no edit)
Another thought comes to mind, since precompiling makes sense for repetitious queries wouldn’t it be safe to say that such framework elements of a site should use compiled queries?
I see for instances of sitemaps and queries that are not parameterized that would be the same across all users in the system that this would be a benefit.
What’s the rough profile of Stackoverflow with respect to number of batches/queries per batch? I would image it’s closer to a number of batches with one query each, hence further eliminating the benefit of compiled queries.
Hey Now Jeff,
“scalability nightmares”.?!? I didn’t know you were a big fan of compiled languages. If S.O. is not as fast as it can be it’s still is a stellar place to go.
Coding Horror Fan,
Catto
That’s the Coding Horror I remember and miss!
All subsequent runs of that same query use the cached query plan and skip the query evaluation. Not so in Linq to SQL. As Rico said, every single run of the Linq query is fully parsed every time it happens.
You are confusing the query plan caching done by SQL Server with translating expression trees to SQL statements done in the client. The former is done for most LINQ to SQL queries too, as they translate to identical SQL statements and only the value of the parameters change. That means SQL Server will simply reuse the cached query plan for almost all LINQ to SQL queries. The latter has to be done for all uncompiled LINQ to SQL queries. Of course, this does not apply to raw SQL statements in the first place (they are already raw SQL). Considering you’re talking about execution plan caching done by the database server for LINQ to SQL generated queries, those sentences are misleading and arguably wrong.
Indeed, every abstraction has a computation cost and makes the code simpler (in theory, at least.) Since most performance problems stem from complex code, in most cases it makes sense to use abstractions.
Good call. I’d consider this in the same ballpark as the .NET vs. C argument. Of course C is technically faster at doing just about anything, but in real life, .NET is “good enough” that it’s worth sacrificing a few milliseconds in favor of faster, more reliable, and easier development.
Great write-up.
At a recent users group in Atlanta, David Scruggs (http://blogs.msdn.com/dscruggs/) showed the benefits of using parallel LINQ queries in .NET 4.0 to gain huge performance gains over regular LINQ. I don’t know if those benefits would carry over to LINQ-to-SQL, but it was quite impressive to see that the process of marshalling a foreach loop to different threads and bringing it all back together was being done for you and providing increased performance. Sure, compiling your own standard queries might be better than uncompiled LINQ queries in a lot of cases, but when you add in parallel processing that’s handled for you, is there even a comparison?
I always figured that LINQ to SQL was slower, but came to the conclusion when you have 10-15 queries on a page, what is an extra 100 ms? I have had mixed success with LINQ to SQL and sub queries from two different contexts, but that’s neither here nor there.
As Joe Enos said, LINQ to SQL is “good enough”
Jeff… you’re back! I hate to say it but I can’t think of a really good coding article you’ve written in a while – however, this one is right back at your peak. Articles like these are why I first started following your blog. Thanks for the post. I look forward to more like this.
Very well put. You addressed my concerns with LINQ and came to conclusions that make a lot of sense.
Very well reasoned. I see lots of developers obsessing over pointless optimizations. I think it is a sign of experience when a developer learns to trust the platform. An example I’ve seen recently is someone going to great lengths to avoid disk accesses to a relatively small file with a complicated caching algorithm, completely ignoring the fact that Windows had already cached the entire file for him. He no-op’d out the caching code and, surprise! Same performance 
Dam it. I want my money back.
I thought this was a baiting post about PHP.
@John Wards, funny thing is I believe most ASP.Net developers (of average intelligence that aren’t language fanboys/gurlz) have come to the conclusion that PHP and .Net can be just as fast and as slow as each other. And going along with Jeff’s “micro-optimization theater” post, shaving off 5 ms for an function call isn’t going to do anyone any good, because over 1000 request, thats only 50 seconds.
And lets face it, everyone can spare a few minutes of there time every day
Wasn’t the whole reason you used Linq that regular old SQL wasn’t “good enough” because it was slow?
Now Linq can be slow, but that’s ok, because it’s “good enough.” 
(Just had to give you a hard time)
Compiled queries are thread safe so you save them in static fields and reuse them across requests.
Anyway depending on percentage of CPU the queries take relative to the entire request, I’d say compiling queries might give you a very small percentage of improvement (posibly below 1% on average considering page caching).
Having non compiled queries in you wesite is like having a small piece of intepreted code that runs on every request(just consider you’re running a small piece of PHP) you won’t take a scalability hit because of that, its’ ridiculous. If non compiled queries kill your scalability because they are parsed each run, what about sites written entirely in interpreted languages like PHP, Ruby, Perl, Python, aren’t those scalable at all?
P.S. This is another missuse of the work “scalable”, people don’t seem to know what it means, the word here should have been more heavy weight, compiled or non compiled is not a scalability issue, both of them scale the same way, but one is ligher other is more heavy.