4

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?

ingredient_15939
  • 3,022
  • 7
  • 35
  • 55

2 Answers2

4

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.

Alex K.
  • 171,639
  • 30
  • 264
  • 288
  • Thanks, perhaps I should add the other reason I ask is from reading 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? – ingredient_15939 Nov 17 '11 at 01:56
  • For Simple Parameterization the parameter length doesn't matter as the maximum size of that type is always assumed, I thought this was the case for sqlcommand params but apparently its not; http://msdn.microsoft.com/en-us/magazine/ee236412.aspx#id0070056 so your assumption about different plans is correct, and my answer only holds true for fixed size types or variable size types where the same size is consistently specified. – Alex K. Nov 17 '11 at 11:34
0

Query hints can be added to your SP that control when - how - QEP will be created. This may not solve your particular issue (or indeed answer your specific question!) however a lot of info on the web about this e.g. http://msdn.microsoft.com/en-us/library/ms190727.aspx

Alan Stephens
  • 569
  • 5
  • 14