July 27, 2009 at 9:24 am
We have a stored procedure, with 9 parameters, that runs significantly slower than running the same code in a query window (7 seconds vs 1 second). A developer here found a suggestion that using "decoy variables" in the SP might speed it up, and sure enough ! The SP runs fast now
CREATE PROCEDURE [dbo].[StoredProcedure]
(
@var1 varchar(50)
, @var2 int = NULL
, @var3 varchar(50) = NULL
, @var4 datetime
, @var5 int = NULL
, @var6 int
, @var7 int
, @var8 bit = 0
, @var9 bit = 0
)
AS
DECLARE @var1_decoy varchar(50)SET @var1_decoy = @var1
DECLARE @var2_decoy intSET @var2_decoy = @var2
DECLARE @var3_decoy varchar(50)SET @var3_decoy = @var3
DECLARE @var4_decoy datetimeSET @var4_decoy = @var4
DECLARE @var5_decoy intSET @var5_decoy = @var5
DECLARE @var6_decoy intSET @var6_decoy = @var6
DECLARE @var7_decoy intSET @var7_decoy = @var7
DECLARE @var8_decoy bitSET @var8_decoy = @var8
DECLARE @var9_decoy bitSET @var9_decoy = @var9
then use the decoy variable in the code instead of the original variable
Any ideas why there might be a difference, and why decoy variable would help ?
July 27, 2009 at 9:30 am
I believe the issue you were experiencing is called Parameter Sniffing. There is a really good article here:
http://omnibuzz-sql.blogspot.com/2006/11/parameter-sniffing-stored-procedures.html
That explains it far better than I can..
CEWII
July 27, 2009 at 1:01 pm
Thanks !
July 28, 2009 at 5:31 am
Yes parameter sniffing seems to be the case here. Look at this article although it does not direcly concern with parameter sniffing. http://www.sommarskog.se/dyn-search-2005.html
I think what a decoy variable does is it gives sql server something to relate to the query plan in the cache and use it. Not very sure about this though.
"Keep Trying"
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply