June 9, 2008 at 7:59 am
I have having difficulty with a proc that takes a long time to execute. This proc is basically 2 selects; 1 very short one that takes less than a second that just gets a value from a table based on the user input and stores that value in a variable (@GUID) and uses this variable in a second huge statement. This 2nd statement is what takes up all the processing time. The funny thing is if I replace the variable with the value I get when running the first SELECT by itself, the second statement runs very quickly. Example code:
DECLARE @GUID HVCIDdt
SELECT
@GUID = ObjectGUID
FROM VisitListJoin_R
WHERE JobID = @JobID – this is the parameter passed to the proc
SELECT DISTINCT
cv.visitidcode,
YEAR(cv.admitdtm) admitYear,
LEFT(DATENAME(month, cv.admitdtm), 3) admitMonth,
DAY(cv.admitdtm) admitDay
-- .. (lots more fields being used)
FROM client c WITH (NOLOCK)
INNER JOIN clientvisit cv WITH (NOLOCK)
ON c.guid = cv.clientguid
AND cv.visitstatus <> 'PRE'
AND cv.visitstatus <> 'CAN'
INNER JOIN SXAAMVisitRegistration vr
ON cv.GUID = vr.clientvisitguid
--… (lots more joins)
WHERE (cv.typecode = 'Emergency')
AND cv.GUID = @GUID --9000061666700270
AND cv.Active = 1
Here is the important part. If I change the Select statement to use the hardcoded number (9000061666700270) the query takes about 5 seconds to execute. If I used the variable @GUID the query takes 1 minute 20 seconds. I have verifeid that both the column GUID in the clientvisit table and the variable @GUID are defined as HVCIDdt which is a used defined datatype as in
CREATE TYPE [dbo].[HVCIDdt] FROM [numeric](16, 0) NULL
I guess this means no data conversion, right?
So why is is so much faster with the hardcoded value than with the variable? Is there a better way to do this? This runs in SQL 2000 SP4
Francis
June 9, 2008 at 12:32 pm
I had the same problem once but i barely remember what i did 😛
Anyway you could try to put that variable into a @table and to make the join in the big query
also you could try to use a GROUP BY instead the DISTINCT
June 9, 2008 at 12:49 pm
You've run into a classic case of parameter sniffing (or more accurately, the lack thereof). See this article[/url] for some details on the cause.
Recommendation: Split into 2 stored procs. Pass the value from the first query as a parameter to the second stored proc. Use that parameter in the query. Optimiser can sniff that, can make a better estimate of rows and should produce a fast plan.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 9, 2008 at 2:24 pm
Thanks for the ideas. I will make some changes and continue testing.
Francis
June 10, 2008 at 11:59 am
perhaps doing a dynamic EXEC of the big query you could obtain the same efect than doing another SP, even better because in the dynamic query the variable would be transformed into a constant value.
June 10, 2008 at 12:01 pm
It can, but then you have all the downsides of dynamic SQL. (permissions, proc cache growth, possible SQL injection)
For something like this, probably 2 procs is easier. For something like a search query where there are numerous possible parameters I would normally recommend dynamic SQL
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 10, 2008 at 12:36 pm
what Gila said also make sense. it is something that you have to consider when using dynamic sql
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply