Post reply

Query plan changes by usage of variables instead of values, Why?

  • September 27, 2010 at 3:13 am

    #224431

    Hi, I have the below query:

    SELECT sms.SenderRef

    , LEN(sms.SenderRef)

    , LEFT(sms.SenderRef, 12)

    , SUBSTRING(sms.SenderRef, 13, 7)

    , sms.Phone

    , Tarih

    , Text

    FROM GB_Basvuru.dbo.tblSMS sms WITH (nolock)

    LEFT OUTER JOIN GB_Basvuru.dbo.CepIlkMesajTarih WITH (nolock)

    ON sms.Phone COLLATE Turkish_CI_AI = CepIlkMesajTarih.Phone

    WHERE (CepIlkMesajTarih.Phone IS NULL)

    AND (Tarih > '2010-09-25 00:00:00')

    AND (sms.Sender <> 'KKM ')

    AND (sms.Sender <> 'ILVPN')

    AND (sms.Sender <> 'MKRD ')

    AND (sms.Sender <> 'KRD ')

    AND (sms.SenderRef <> '19183')

    which runs in 3 seconds. If I chance '2010-09-25 00:00:00' with @Date1 as:

    DECLARE @Date1 AS DATETIME

    SET @Date1 =cast('2010-09-25 00:00:00' as datetime)

    SELECT sms.SenderRef

    , LEN(sms.SenderRef)

    , LEFT(sms.SenderRef, 12)

    , SUBSTRING(sms.SenderRef, 13, 7)

    , sms.Phone

    , Tarih

    , Text

    FROM GB_Basvuru.dbo.tblSMS sms WITH (nolock)

    LEFT OUTER JOIN GB_Basvuru.dbo.CepIlkMesajTarih WITH (nolock)

    ON sms.Phone COLLATE Turkish_CI_AI = CepIlkMesajTarih.Phone

    WHERE (CepIlkMesajTarih.Phone IS NULL)

    AND (Tarih > @Date1)

    AND (sms.Sender <> 'KKM ')

    AND (sms.Sender <> 'ILVPN')

    AND (sms.Sender <> 'MKRD ')

    AND (sms.Sender <> 'KRD ')

    AND (sms.SenderRef <> '19183')

    Then execution time is 49 seconds. The respective query plans are different also, is there a reason behind this behavior? How using variables effects query execution?

    Thanks.

  • September 27, 2010 at 3:27 am

    #1227581

    http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 30, 2010 at 6:26 am

    #1229553

    Thanks Gila, that clarified the issue.

    Serter

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply