July 17, 2002 at 8:56 am
Hello,
I am having a puzzling issue here. Can someone explain to me why code in a stored procedure would be faster running as code in Query Analyzer as opposed to running as a stored procedure?
Code in QA takes approximately three minutes to return. But running the same code as a stored procedure takes much longer than that.
Does anyone have any idea? We're all stumped.
As always, Thank you very much,
Melanie
July 17, 2002 at 10:08 am
I am making the assumption that it always runs slower, even when it has a chance to get its execution plan.
A couple of questions:
When the stored procedure is called, it is being called with the owner (i.e. dbo.usp_MyStoredProcedure)? If not, you may get a cache miss when the execution plan is looked for. This actually requires an exclusive [COMPILE] lock to be put on the object, so if you are running it across multiple connections, you may be serializing the stored procedure.
Is the stored procedure named sp_<rest of name>? If so, you are going to get the same problem. By default, SQL Server is going to first look in the master database for all stored procedures beginning with sp_ as those are for system stored procedures.
One thing you might do is turn on profiler and look for SP:CacheMiss and SP:Recompile. This may explain why you are getting longer times than normal.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
July 17, 2002 at 10:10 am
Are you running with exactly the same parameters each time? If not there is a chance that the compiled plan works well with one set and not the other. In some cases you may need to add the with recompile option to over that problem.
Andy
July 17, 2002 at 10:33 am
Hello,
The stored procedure is not named sp_XXX. Rather, it is named spXXX. Also, I am running the code with the same parameters each time.
I ran the two tests again (as code and as an SP) and came back with the SP being at least a third faster. The time of day could also be a factor. It's around lunchtime now and the server is not to busy. I'll try this tomorrow morning (when everyone is running their reports) to see what my processing times are.
Thank you for you insight!
Melanie
July 17, 2002 at 11:08 am
Also when you run each way output the Execution Plan and see if they differ in any way.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply