August 21, 2009 at 8:18 am
Hi,
I have a job which is scheduled on the server that runs for every 5mins. The job just executes one of the storedprocedure which pulls the data from one table to other table. The storedprocedure uses the cursors.
So my question is, when I run the storedprocedure manually its taking far lesser time than running it from Job. So why there is a time difference in running? Both should take same time more or less. But the difference is almost one hour. What could be the reason behind this?
Thanks!
August 21, 2009 at 8:35 am
Unfortunately there is no real way for us to tell you what the problem is based on your post. For performance issues, please read and follow the instructions in the second article in my signature block.
Also, if you are moving data between tables in a database, why are you using a cursor. I'd bet dollars to doughnuts that a more efficient set-based solution would perform much better.
August 21, 2009 at 10:56 am
Any time you see radical differences in performance, you need to identify the differences. Are you passing different parameters? Are you testing it on a different server or with different data? Are the default connection settings of the server & your client software the same?
Plus, you might just be running into contention, meaning that other people are using the resources that you need when you need them.
Like Lynn says, without a lot more detail we're just guessing here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 26, 2009 at 6:48 pm
I want to emphasize what Lynn said about the cursor - WHY are you using a cursor to move data from table to table?
Besides the article that Lynn suggested, please read the articles by Jeff in my sig block.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply