SQL2000 Stored Procedures run WAY slower than straight TSQL Queries?

Question

SQL2000 Stored Procedures run WAY slower than straight TSQL Queries?

Jason Clark-204235

Valued Member

Points: 66
More actions
January 26, 2005 at 6:43 am

#84685

I have an issue that just began in the past week. I have a stored procedure that takes approximately 2:30 seconds to run when executed as an SP, but if I pull the code into Query Analyzer and run it as a long T-SQL statement and hard-set the parameters, it takes 3-4 seconds. Watching the Trace and the Execution plan, it appears to hang up on one line of the stored procedure for a good 2 minutes when run as a SP, and if I pull that code, the runtime drops to 20 seconds, but that's still too long (it's used to populate an Intranet Web App).
If I run that code bit by itself, it executes IMMEDIATELY. Is there anything that would cause the Stored Procedure to run that slowly?

Viewing 12 posts - 1 through 11 (of 11 total)

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

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 1

Are there any locking or blocking issues with regards returning the data? Is it possible that the proc is being called mulptiple times and blocking itself?

What does that line do?

Shamless self promotion - read my blog http://sirsql.net

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 2

- did you take a look at locking ? maybe http://www.sqlservercentral.com/scripts/contributions/826.asp can help out.

- Maybe executing the sql with (nolock) hint will deliver sufficient data.

(be aware of uncommitted data !)

- hardset parameters ? How ?

select * from .. where mycol = 'abc'

or

declare @param1 varchar(15)

set @param1 = 'abc'

select * from .. where mycol = @param1

access paths may vary !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jason Clark-204235 Valued Member Points: 66 More actions · Answer 3

Not that I can tell, and I would assume that if there were locking/blocking issues, they would occur when run as TSQL statements as well.

The line in question merely sums two columns, and divides the first by the second. It's not an advanced query by any means. Further the sp does not call itself, and shouldn't be getting called multiple times.

BTW, I've reindexed the table that the statement calls from due to my concern that it was an issue with indexing, but no such problem, as that statement executes immediately when called alone.

Jason Clark-204235 Valued Member Points: 66 More actions · Answer 4

Not locking (as stated above), as far as I know, but I'll try the nolock, just in case.

And I'm using the latter rather than the former for hardset parameters. I declare the parameters and set them at the beginning of the TSQL statement, which AFAIK should operate nearly the same as passing them into the SP.

I'll check the nolock thing and see if it makes a difference. Thanks!

Nicholas Cain SSC-Dedicated Points: 33009 More actions · Answer 5

If there is another query that is running against the table/s containing those columns then you could end up with a blocking problem, if the data is not highly transactional you could look at using the (nolock) hint in the query, it would remove that potential problem.

Shamless self promotion - read my blog http://sirsql.net

alkanfer SSC Veteran Points: 268 More actions · Answer 6

i had the same problem once.

The problem might be wrong execution plans.

a)Sometimes query optimizer chooses wrong plans.

b)You might consider changing variable data types

(Because if the data type of the variable and the data type of actual column is not same query optimizer doesn't use indexes and may scan all of the table)

1) You might alter the procedure with " WITH RECOMPILE " option

2) You might update your statistics. sp_updatestats

Jason Clark-204235 Valued Member Points: 66 More actions · Answer 7

Excellent information, thanks!

I took an easy way out, and it actually worked... I copied all of stored procedure into a new one, name tmpusp, ran it, and it ran fine. I then got rid of the old one, renamed the new one, and it solved the problem.

rschaeferhig SSCrazy Eights Points: 8776 More actions · Answer 8

Sounds like you had a bad/old copy of the SP in the cache. The WITH RECOMPILE option might have fixed that. Or you could have cleared the cache with a DBCC command.

pat-748507 Grasshopper Points: 14 More actions · Answer 9

I think the whole process is as easy as running

sp_recompile theBadSpName

you dont need to surround the sp in ''s

it will then be marked for a recompile the next time it runs.

after that I should run fast.

Johan Bijnens SSC Guru Points: 135382 More actions · Answer 10

Jason Clark (1/27/2005)
... renamed the new one,....

Just one remark:

Keep in mind that if you generate sql scripts for that db, because you renamed the sp, sqlserver did not change the "create proc" text in syscomments !!