Marco Russo posted about a possible LINQ to SQL performance problem.
Using AdventureWorks for our sample, we might have a stored procedure to select from the Production.Product table:
[viewcode] src=”http://facility9.com/wp-content/uploads/2008/08/create_getproductbyname.sql” showsyntax=”no” link=”yes” [/viewcode]
Calling this procedure will always use the same cached execution plan and will only be re-compiled if/when the procedure is consciously changed by a DBA or developer.
Using LINQ, we would have a query like:
string s = "ML Mountain Frame-W - Silver, 38";
var query =
from x in db.Products
where x.ProductName == s
select x;
string s2 = "Stem";
var query =
from x in db.Products
where x.ProductName == s
select x;
In the first query, s has a length of 32 and the resulting SQL would be executed using a parameter of type VARCHAR(32). In the second query, s2 has a length of 4 and would, thusly, be a VARCHAR(4).
When both of these queries are run through the database using sp_executesql, two separate execution plans are generated. There are a total of 29 different lengths of string in the Production.Product table. In order to avoid query compilation times, 29 different execution plans would have to be kept resident in memory, and that’s just for the Name column of the Product table! When you consider the sheer number of columns and tables in the AdventureWorks database, that’s a large number of execution plans that would have to be cached in the database. It’s certainly possible with a large enough amount of RAM, but it’s not the optimal choice.
The moral of the story is “Be wary when using LINQ to SQL”. It certainly has a place in the application development process, but there is a distinct price that you’re paying for the convenience.
