Post reply

Variable causes extreme slowness - why

  • July 7, 2011 at 4:53 pm

    #243944

    I have a simple proc to build a temp table that then does some archiving. When I use a variable in the temp table build it never finishes. I let it run for 22 hours before I gave up and cancelled. If I hard code in the value it runs in under a second. The table that the temptable is being created from has just under a million records - if that matters.

    declare @current_timestamp datetime

    set @current_timestamp = current_timestamp - 1095

    Select X into [#temptable] from

    Where (X in (SELECT X FROM tblSample

    WHERE(dtCrTstamp <= @current_timestamp)))

    This never completes

    Select X into [#temptable] from

    Where (X in (SELECT X FROM tblSample

    where dtCrTstamp <= CONVERT(DATETIME, '2007-07-1 00:00:0)))

    This takes less than a second

    What is causing this? I'm thinking it has to do with indexing but the table is not indexed on the dtCrTstamp field so ???

    Perplexed :hehe:

    Thanks

  • July 7, 2011 at 4:57 pm

    #1350097

    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

  • July 12, 2011 at 4:57 pm

    #1351938

    Thanks for the link. Any way to get around this via hints or ?

  • July 13, 2011 at 12:47 am

    #1351968

    Frankie-464050 (7/12/2011)

    Thanks for the link. Any way to get around this via hints or ?

    Yes. Read the article Gail pointed you at. 😉


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • July 13, 2011 at 8:45 am

    #1352197

    LutzM (7/13/2011)

    Frankie-464050 (7/12/2011)

    Thanks for the link. Any way to get around this via hints or ?

    Yes. Read the article Gail pointed you at. 😉

    Use the hard-coded value! i.e. dynamic sql if it needs to be in reusable TSQL. Be sure to guard against SQL Injection!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 5 posts - 1 through 4 (of 4 total)

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