March 15, 2004 at 7:12 am
Hi,
What is better: to embed your TSQL code directly within a job step (the command window) or to have the job step call a stored procedure ie: exec usp_sendmail (from the command window)? Is there any job performance or management issues/differences?
Thanks. Jeff
Many thanks. Jeff
March 16, 2004 at 8:10 am
Your choice Jeff. The advantage of a stored proc is that it can be used in more than one job, tested more easily if changes are required. It may be easier to recreate the job on another server as a stored proc. I would generally use stored proc, but if there is very little code and I know I don't need this code anywhere else I'll embed the TSQL directly in a job step. You may also want to consider the frequency of the job. If the job runs slowly multiple times a day during the time the system is being used then maybe a stored proc is more efficient. On the other hand if the job runs quickly at a time when none else uses the system then efficency may not matter.
Thats my 2 cents
Francis
March 17, 2004 at 8:42 am
Stored procedure. It is easier to handle errors in a stored procedure then in the job. If the stored procedure returns an error then you can log it to a process tracker and the job can continue with other steps if desired.
Think of a "Job" as just that, a process that implements "Tasks" with the "tasks" being stored procedures.
March 21, 2004 at 5:22 pm
I have a similar problem. I have a stored proc that I want to run from a job but part of the stored proc may generate and "handle" an unique index violation (by "handle" I mean we detect the index violation via an insert statement failure and alter the value of one of the indexed columns (called "Duplicate", a GUID, just denotes a duplicate record) data and attempt to reinsert).
The upshot is that an error is raised by SQL server in the proc ("(Error 2601) Cannot insert duplicate key row in object 'Mytable' with unique index 'IX_Mytable_4'.") but we continue processing the remainder of the proc to handle the error condition.
This is fine when run from ISQL but if I was to run the proc from a scheduled job the job detects the raised error and stops processing (it considers the step has "Failed").
How can I prevent the error being detected by the JOB (sql server agent)? Can I clear the error(s) in the Proc? Can I tel the Job to ignore errors? (I know I can control which steps run depending on the result of a previous step but the job in this case is a single step.)
My fall back solution will be to test for the index violation myself (using if (select count(*) from Mytable where indexedcol1 = @data1...and indexedcolN = @dataN) > 0) but I think that would be very slow ,which is why we attemp the insert against the index and check for the insert failing.
March 22, 2004 at 8:02 am
What about using @@error as in:
IF @@ERROR = 2601
print "A index constraint violation occurred"
Francis
March 22, 2004 at 2:22 pm
fhanlon: Using @@error to detect the error is fine but it doesn't prevent the error condition being raised and detected by the Job/Server Agent. I haven't tried the obvious of setting @@error = 0 since I assume that the variable is readonly and it is reset anyway as soon as it is accessed like @@rowcount.
I tested the fall back solution I suggested above and unsurprisingly the query performance (time executing) went up about 50%. I then went mad and turned the duplicate test into dymanic SQL so instead of running queries like "select count(*) from Mytable where col1= @data1 and col2 = @data2"...etc (there are half a dozen cols in the index to check) to test for an existing row I insert into #tmp exec(@SQL) where @SQl = "select count(*) as dup_count from Mytable where col1= '123' and @col2 = '1 mar 2004'"....etc. The query performance with dynamic sql was about the same as the original where an index was failing to alert us of the duplicate???? Cool.
I have come accross this situation a number of times where a select query with a number of conditions in the where clause runs much faster as dynamic SQl than as a simple select with parameters. It must be the query compiler being unable to use the correct index when params (variables) are used, but its fine when the dynamic sql uses what are essentially inline or hard_coded clauses.
Note: the where clause in all situations mirrors the index as far as order of columns is concerned and I had 1.1 million rows in Mytable to do the duplicate testing.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply