April 29, 2002 at 11:37 am
I have a stored procedure that takes seconds to run in SQL Analyser, but takes 20 minutes or more to run as a job in SqlServerAgent.
I commented out all print statements, set up a log file for the job step and still no joy. The job does completes properly without errors, but after a long time. The SP runs in seconds if I try running it from SQL Analyser before or after running the job.
Any suggestions?
Thanks.
April 30, 2002 at 5:58 am
Can you script your job using EM so we can look at how it is defined. May be something there? Also try starting the job and watching under Profiler.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 8:15 am
I tried using "Set nocount on" in the SP and would you believe it, the problem was solved.
The conclusion is that the SP runs ten times slower as a job in SQLServerAgent unless this option is set.
BTW the SP uses a cursor to read rows from one table and then compares each row against another table, before inserting into that other table. The data in each row is also validated using isdate(), etc.
I don't understand how it done it, however I would appreciate anyone's input on this.
Thanks.
April 30, 2002 at 9:15 am
This is possible due to the counts returning in the buffer to SQLAgent. May be reading each one and if you have a return each loop thru the cursor, then the more you have the more outputs it reads thus slowing it down looking for an error or some other piece of status info it may store in the log it keeps.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
April 30, 2002 at 10:12 am
Strange. Never seen anything like this and I use SQLAgent all the time. Antares has a reasonable explanation that I'd buy.
Steve Jones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply