socialmedian: LINQ to SQL queries involving strings cause SQL Server procedure cache bloat

Hi there. I'm Jason, one of the founders of socialmedian.

socialmedian delivers the news, filtered by your network.

We'd love to have you join in.

You can use facebook connect to sign in.

Add News Flash

User-submitted headlines for this story

Clip

LINQ to SQL queries involving strings cause SQL Server procedure cache bloat

Source: Data Management

Aug 28, 2008

Summary

Adam Machanic created an item on connect explaining how LINQ to SQL queries involving strings cause SQL Server procedure cache bloat

If an application is using LINQ to SQL and the queries involve the use of strings that can be highly variable in length, the SQL Server procedure cache will become bloated with one version of the query for every possible string length. For example, consider the following very simple queries created against the Person.AddressTypes table in the AdventureWorks2008 database:

c#Line number On/Off | Show/Hide | Select all

var p =
from n in x.AddressTypes
where n.Name == "Billing"
select n;
var p =
from n in x.AddressTypes
where n.Name == "Main Office"
select n;

Code is hidden, SHOW

If both of these queries are run, we will see two entries in the SQL Server procedure cache: One bound with an NVARCHAR(7), and the other with an NVARCHAR(11). Now imagine if there were hundreds or thousands of different input strings, all with different l

...Read the full article

Comments (0)

Add Your Comment