May 16, 2005 at 5:30 am
Can anyone point me at an explanation/best practice guide for the following please?
We've reduced the execution time on one of our queries from over 30 seconds to 2 seconds simply by re-declaring the parameters inside the stored procedure ie, from this
CREATE PROCEDURE iti_sp_GetBalance_By_CostCentre @C varchar(50), @co varchar(20), @r varchar(20), @y int, @p int
and then using the parameters within the select statement to this
CREATE PROCEDURE iti_sp_GetBalance_By_CostCentre @C varchar(50), @co varchar(20), @r varchar(20), @y int, @p int
AS
declare @company varchar(50), @contract varchar(20), @region varchar(20), @yr int, @pr int
set @company= @C
set @contract=@co
set @region=@r
set @yr=@y
set @pr=@p
The execution plan notes something about a bookmark lookup but doesn't make any sense to me!
Thanks
Andy
May 16, 2005 at 6:37 am
Basically, a Bookmark lookup is when sql server scans an index to satisfy a where condition, once the index is scanned, it starts retreving the data from the table using the bookmarks found in the index (bookmark lookup).
The speed difference you are experiencing is caused by parameter sniffing. While I don't fully understand this phenomenum, you can search on these forums for more details. The part I understand is that the compiled plan is created in function of the first set of parameters used to call the sp. But once in a while you get a situation where you need a few different plans depending on the parameters, and if the wrong plan is used you get a huge performance hit.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply