robert.gerald.taylor (9/6/2013)
nick947 (8/21/2013)
Do you know any other tricks to get past parameter sniffing?Nick, here's a link explaining parameter sniffing and how to work around it:
It is probably worth trying to change your query so that you assign the parameters to internally defined variables and then use those internal variables in the rest of your query (as opposed to using the parameters directly).
HTH,
Rob
Hi Nick,
I have had to do what Rob suggested with the internal parameters. That worked for us.
So you have parameters passed to the stored procedure, then you declare variables at the top of the stored procedure and assign the parameter values to them, and use the local variable in the code...
example:
Alter Procedure dbo.My_Stored_Proc (@Var1 int, @Var2 varchar(50))
AS
Declare @Local_Var1 INT, @Local_Var2 varchar(50)
Set @Local_Var1 = @Var1
Set @Local_Var2 = @Var2
/****
code block
*****/
WHERE database_field1 = @Local_Var1
and database_field2 = @Local_Var2
Hope that helps,
Sarah