Post reply

Query Analyzer vs Stored Procedure.

  • August 27, 2002 at 1:24 pm

    #101235

    I have a query in query analyzer that runs 27 times faster that the same query after I create it as a stored procede. What could be the difference?

    Thanks!

  • August 27, 2002 at 2:48 pm

    #435477

    The query in QA is faster? It may be a recompile issue on the stored procedure. Here are some of the more common reasons for a recompile:

    • Stored procedure prefixed with sp_ and not in master database
    • Owner name not specified with executing the stored procedure (for instance, user johnd is executing a stored procedure owned by dbo)
    • Use of certain SET commands
    • Use of temporary tables without using OPTION(KEEPFIXED PLAN)

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • August 28, 2002 at 2:39 pm

    #435557

    It is faster in QA. I have tried the option (keepfixed plan) and it does not help. It also does not seem to help to recompile the SP. The SP does use 5 differenct Temp tables. Any help is greatly appreciated.

  • August 28, 2002 at 4:39 pm

    #435571

    You don't want to stored procedure to recompile unless it's necessary, which in most cases it is not. You might turn on Profiler and monitor the the various Stored Procedure events like SP:CacheHit, Sp:CacheMiss, SP:ExecContextHit, and SP:Recompile to see if a recompile is in fact happening and how many times during the course of your one stored procedure it is happening (in which case you're going to need to turn on statements as well).

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • August 28, 2002 at 4:52 pm

    #435575

    I ran the profiler and it appears that every time the SP is executed via the .NET ASP app it recompiles and loses all performance. I can set to recompile in the QA and then run as many times as I like and it runs very fast until someone runs it from the .NET app. Is there any way to tell a SP not to recompile? I know there is a way to force it to recompile.

  • August 28, 2002 at 5:01 pm

    #435578

    It is odd that the .NET application is forcing a recompile when QA does not. With the execption of the OPTION() where you've already specified KEEPFIXED PLAN, there isn't much else to stop it from recompiling.

    Is the .NET app passing parameters which are drastically different from the way you're calling it in QA? Is the .NET app forcing recompiles on other stored procedures?

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

    K. Brian Kelley
    @kbriankelley

  • August 29, 2002 at 2:15 am

    #435600

    Is the .NET app possibly making changes to the structure of any items the query touches, thus forcing the sp to recompile?

    - Troy King


    - Troy King

  • September 2, 2002 at 10:38 am

    #435804

    Are you calling the SP from Query Analyzer, or running the code of the SP in QA?

    If it's the latter, are you declaring all the variables passed into the SP as variables in the code in QA? If you hard-code values passed to a query, the query optimizer may choose a different (better) execution plan than if you pass in the values as variables.

    e.g.

    declare @intVar as int

    set @intVar = 2

    select * from ATable where AValue = @intVar

    may execute differently from

    select * from ATable where AValue = 2

    Chris

Viewing 8 posts - 1 through 7 (of 7 total)

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