|
Posted by Robert Klemme on 10/30/06 09:13
On 29.10.2006 04:45, Catch_22 wrote:
> 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.
Maybe the DB engine just runs out of buffer cache. Since table A is the
large one it may be that once your process gets to table B it has too
many dirty buffers and needs to flush them to disk before it can
continue processing. IMHO you should be able to detect that situation
if you look at physical IO (either with profiler or perfmon).
Kind regards
robert
[Back to original message]
|