SQL Server Performance

Variable in Where clause slowing me down

Discussion in 'T-SQL Performance Tuning for Developers' started by fhanlon, Jun 6, 2008.

  1. fhanlon New Member

    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 then 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?
  2. madhuottapalam New Member

    couple of suggestions...
    (a) Replace teh declaration DECLARE @GUID HVCIDdt with DECLARE @GUID HVCIDdt [numeric](16, 0) and check
    (b) drop the procedure and recreate or use with recompile option to check the issue is not related to Prameter sniffing.
    thanks
    Madhu

Share This Page