SQL SERVER Procedure Inconsistent Performance

Question

I am working on a SQL Job which involves 5 procs, a few while loops and a lot of Inserts and Updates.

This job processes around 75000 records.

Now, the job works fine for 10000/20000 records with speed of around 500/min. After around 20000 records, execution just dies. It loads around 3000 records every 30 mins and stays at same speed.

I was suspecting network, but don't know for sure. These kind of queries are difficult to analyze through SQL Performance Monitor. Not very sure where to start.

Also, there is a single cursor in one of the procs, which executes for very few records.

Any suggestions on how to speed this process up on the full-size data set?

Answer 1

I would check if your updates are within a transaction. If they are, it could explain why it dies after a certain amount of "modified" data. You might check how large your "tempdb" gets as an indicator.

Also I have seen cases when during long-running transactions the database would die when there are other "usages" at the same time, again because of transactionality and improper isolation levels used.

If you can split your job into independent non-overlaping chunks, you might want to do it: like doing the job in chunks by dates, ID ranges of "root" objects etc.

Answer 2

I suspect your whole process is flawed. I import a datafile that contains 20,000,000 records and hits many more tables and does some very complex processing in less time than you are describing for 75000 records. Remember looping is every bit as bad as using cursors.

I think if you set this up as an SSIS package you might be surprised to find the whole thing can run in just a few minutes.

With your current set-up consider if you are running out of room in the temp database or maybe it is trying to grow and can't grow fast enough. Also consider if at the time the slowdown starts, is there some other job running that might be causing blocking? Also get rid of the loops and process things in a set-based manner.

Answer 3

Okay...so here's what I am doing in steps:

1. Loading a file in a TEMP table, just an intermediary.
2. Do some validations on all records using SET-Based transactions.
3. Actual Processing Starts NOW.

TRANSACTION BEGIN HERE......

LOOP STARTS HERE

a. Pick Records based in TEMP tables PK (say customer A).

b. Retrieve data from existing tables (e.g. employer information)

c. Validate information received/retrieved.

d. Check if record already exists - UPDATE. else INSERT. (THIS HAPPENS IN SEPARATE PROCEDURE)

e. Find ALL Customer A family members (PROCESS ALL IN ANOTHER **LOOP** - SEPARATE PROC)

f. Update status for CUstomer A and his family members.

LOOP ENDS HERE

TRANSACTION ENDS HERE