Posted by Catch_22 on 10/29/06 03:45

Hi,

I have a large SQL Server 2000 database with 3 core tables.

Table A : 10 million + records
Table B : 2 million + records
Table C : 6 million + records

One of the batch tasks that I have to perform firstly builds a list of
all keys for records from each of the three tables that I need to
process (along with a flag to tell me which table the key is from).
This list is populated into a table variable.

I then loop through the table variable to process all the records with
the particular key value.
The updates are run in order of the tables .... Table A first, B next
and finally C.

The table variable will typically hold 3000 keys.

My problem is this ...... the processing of the key records from Table
A runs well - it takes around 40 minutes which is acceptable for the
level of processing being carried out. Though when I start processing
the transactions for Table B the first couple of statements execute
successfully though then the subsequent statements take a long time (in
some cases hours) to complete. The format of the statements for all
tables is virtually the same and the tables have been indexed
appropriately.

The thing is that if I alter the stored proc to only process records
from Table B or Table C ... the procedure flies through and processes
the records in a flash .... 1-2 minutes.

Can anyone suggest what might be the issue here ?
I have read many posts though can't seem to find the solution.
Should I break up my processing so that it processes each table
individually ?
I've tried running the Profiler though it doesn't provide me with much
in the way of solutions.

Regards,
Ian

[Back to original message]