Brennan’s Blog

Last week I presented Deeper in LINQ for the Wisconsin .NET User Group and concluded with a speed test. The speed test compared ADO.NET to LINQ. The ADO.NET method called a stored procedure while the LINQ method generated the query inline. The results showed that LINQ is not as fast. The overhead is due to the LINQ for SQL provider that has to parse the query within the CLR and then generate the SQL that it sends over to SQL Server. The generated SQL is identical to what is in the stored procedure used by the ADO.NET method. The difference in timing is purely from preparing the query. And since LINQ to SQL uses ADO.NET once it generates the SQL it cannot possibly run faster than a direct ADO.NET implementation. The speed test was simply to show the difference.

[ Download the sample project: DeeperInLINQ.zip ]

In the attached screenshot you can see three tests. I wanted to make a LINQ sample that runs much faster so I added the second test to show how long it would take to have LINQ call the stored procedure just like the ADO.NET method and avoid the overhead of preparing the query. It seems that even though the original LINQ method is running the same query over and over it still has to carry out the preparation for the query each time. This is clearly an area that could be tuned prior to the .NET 3.5 release. In the meantime, the call to the stored procedure allows the new LINQ method to perform nearly as well as the ADO.NET version. If this particular method was performing poorly in a real application, breaking out the work to a stored procedure would be a good tuning option. But most of the time you will want to continue with inline LINQ queries so you get the productivity benefits that it provides through Intellisense and other features.

LINQ has put the spotlight back on a classic compromise. As you add a layer of abstraction you sacrifice performance for productivity. The same has been true many times before. For example, the same situation happened with assembly versus C and later with C versus scripting languages like Perl. When I did a lot of CGI programming with Perl back in 1998 the argument between C and Perl focused on the fact that you could complete a working application in a fraction of time that it took to build it in C. Sure C could do it all faster, but you would have to spend the time to line up everything just right. In Perl you can just get it done and rely on the compiler and runtime to optimize the code and Perl happens to do that very well. And as more optimizations are incorporated into newer versions of the Perl compiler, scripts can automatically perform better without any direct changes. The same is true for C# and will be true for LINQ.

When I have to write several methods that pull data from the database I can do so quickly and rely on LINQ to generate queries that are better than if I hastily wrote them myself. And just like with Perl, if something has to be really fast, I can make a call out to another layer that can do it faster. In Perl it is common to bind to C libraries to do graphics and cryptography work. For LINQ, moving to a stored procedure is an easy improvement. This may only be necessary for a small subset of the methods you create that use LINQ, so write them with just LINQ to start and wait for performance problems to reveal themselves. Once you can pinpoint them you can always make simple adjustments, especially if you have a carefully encapsulated data access layer.

For a jump start into improving performance with LINQ, you can turn to Rico Mariani. He is a a leading expert on performance and has written a five part series on tuning LINQ.

• Part 1 - Performance comparison
• Part 2 - Cost analysis
• Part 3 - Per row costs
• Part 4 - Compiled query
• Part 5 - Conclusions

This week I am starting a new side project that I hope to make public in a few weeks. It will make use of Visual Studio 2008 and .NET 3.5 which includes LINQ as well as the WCF and AJAX integration features. I can already see how much of my work is already done due to the new features in .NET 3.5. I will write more on the project once I am ready for the public beta.