Query Analyzer vs Stored Procedure.

Question

Query Analyzer vs Stored Procedure.

scottdever

SSC Enthusiast

Points: 122
More actions
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!

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

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

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

scottdever SSC Enthusiast Points: 122 More actions · Answer 2

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.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

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

scottdever SSC Enthusiast Points: 122 More actions · Answer 4

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.

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

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

katravax Old Hand Points: 353 More actions · Answer 6

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

- Troy King

croesus Grasshopper Points: 22 More actions · Answer 7

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