Search code examples
sql-serverado.netsql-execution-plan

ADO.NET Commands and SQL query plans

I've been reading up on query plans and how to minimise duplicate plans being created by SQL Server for the same basic query. For example, if I understand correctly, sending queries with inline values may result in 2 different query plans. Oversimplified example:

"SELECT FirstName FROM Members WHERE LastName = 'Lee'"
"SELECT FirstName FROM Members WHERE LastName = 'MacGhilleseatheanaich'"

Using a stored procedure avoids this, as it ensures the query hash will be the same. "LastName" is passed as a parameter, eg:

CREATE PROCEDURE sp_myStoredProcedure
   @LastName varchar(100)
AS
SELECT FirstName FROM Members WHERE LastName = @LastName
Go

Now, my question is whether the same applies to the Command object (eg. SQLClient.SQLCommand in ADO.NET). The reason I ask is that string parameters don't have a defined max length, as in the code above. Take the following code:

MyCmd.CommandText = "SELECT FirstName FROM Members WHERE LastName = @LastName"
MyCmd.Parameters.AddWithValue("@LastName", "Lee")

Then later:

MyCmd.Parameters.Clear()
MyCmd.Parameters.AddWithValue("@LastName", "MacGhilleseatheanaich")

Since @LastName hasn't been declared to SQL Server as having a defined maximum length, will SQL Server create a new query plan for every different value when I execute the command this way?

My question comes mainly from reading how how LINQ2SQL in .NET 3.5 can miss the cache by defining different parameter lengths (http://damieng.com/blog/2009/12/13/sql-server-query-plan-cache). Since lengths aren't defined either when using Command object, would it not share the same problem?

Solution

  • Your paramaterized queries are actually sent to the server as a call to the system stored procedure sp_executesql which is very good at attempting to reuse query plans where possible by comparing a hash of the the unpopulated statement mask with those of plans still live on the cache, so if its possible and if the server thinks its appropriate you have a good chance of plan reuse (and potentially of paramater sniffing problems too).

    Also worth mentioning that Simple Parameterization pretty much guarantees that the two statements in you first example would actually end up using the same plan.