Post reply

Parameters vs Local Variables in SP

  • December 21, 2006 at 2:26 pm

    #153354

    We have an issue where a stored proc runs considerabley slower than if the query is run as a script.  We have narrowed it down to this:  If you use parameters multple times within the SP it runs slow.  If you declare local variables for each parameter within the SP and assign the values from the params to the local vars, the SP runs much faster.  We have two SQL 2000 Servers where this is true and two where this is not true.  In fact, we have two instances of SQL Server 2000 running on one box and this problem occurs on one but not the other.  HAs anyone one else seen this?  Is this a setting on the SQL server?

  • December 21, 2006 at 2:31 pm

    #679563

    first off, are you doing a DBCC DROPCLEANBUFFERS between each test to make sure that the faster run isn't faster because of HDD IO?

  • December 21, 2006 at 2:52 pm

    #679571

    No, we are not running DBCC DROPCLEANBUFFERS between test runs, but this occurs consitently.  We have multiple SPs where this occurs.  The SPs use a param 15 times.

  • December 21, 2006 at 3:09 pm

    #679582

    Search these forums for parameter sniffing.  You'll find plenty of information on the reasons and workarounds.  Post back if you have any further questions.

  • December 21, 2006 at 3:16 pm

    #679585

    Yep, that's it.  Thanks

  • December 21, 2006 at 3:19 pm

    #679587

    HTH.

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

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