Issue with Procedure running as a schedule job
-
I have a procedure which has 2 statements in it
1. to create a table say "Summary"
2. To update this table using the following query.
When i run the procedure in the query anaylzer, it works fine. But when i configure a job for the same procedure, it hangs at the insert statement (I checked this using profiler, where the last statement executed is "stmt:started" for this insert query).
INSERT Summary(Account_ID, Schedule_Date, Owner_ID, Revenue)
SELECT V.AccountID, T2.ScheduleDate, V.OwnerID, SUM(T2.Revenue)
FROM dbo.vw_Summary V
INNER JOIN TABLE1 T1 (NOLOCK) ON T1.OppID = V.OppID AND T1.BookEntryID = @BookEntryID
INNER JOIN TABLE2 T2 (NOLOCK) ON T2.TABLE1_ID = T1.ID AND T2.ScheduleDate >= @SummaryStartDate
WHERE T2.LastModifiedDate =
(
SELECT MAX(LastModifiedDate) FROM TABLE2 WHERE ScheduleDate = T2.ScheduleDate AND TABLE1_ID = T1.ID
)
GROUP BY V.AccountID, T2.ScheduleDate, V.OwnerID
There is one more issue that is noted is..when the procedure is run, the "Stmt:started" for the insert statement appears 2 times in the profiler.
thanks in advance.
Prasanna
-
Between the join to the inserted table (which will cause the WHERE and ON clauses to be totally recalculated for every row inserted) and the correlated subquery in the WHERE clause, this will simply take longer and longer for every row inserted. It's RBAR on steroids!
You say you ran it in Query Analyzer... did it ever actually finish? Also, why do you need the join back to the Summary table? What is it that you're trying to actually accomplish? What is the problem definition for this code?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Firstly thanks for replying. Actually there is no join for the inserted table. Values are picked from 2 different tables and a view (vw_Summary) and dumped into the "Summary" table. The corelated sub-query is for TABLE2, where there can be duplicate records for the joining clause and hence to get a single record of the table. It is a business requirement.
Is there any other way of getting a record from a table where there can be duplicate records on the joining clause and we have to use the max of modified timestamp to identify the record.
Thanks in advance,
Prasanna
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply