Dears,

I have a serious issue with our sybase server [or something related] causing a delete command of 20.000.000 rows to go from 40 minutes to 4 hours of deletion time.

the unix system is an AIX.

We have already checked if there are other jobs using the table where we are deleting our records from , but this is not the case.


The weird thing is that even tough the volume of the daily load is quite constant [+/- 18.000.000 up to 20.000.000] on a total of 80.000.000 rows, suddenly this delete statement starts to take up too much of our time.

Our space division has already checked the I/O on the raw devices and they see a slight increase but not this significant to pinpoint this as the reason for our troubles.

A showplan shows that an index is beeing used, so we do not see a sudden change in plan from index to tablescan ...

Has anyone already suffered the same issues and if so, what to do about it, or has anyone some idea where we might look for , ...disks? settings in sybase...

thanks in advance,

yours,
Davy

do you delete the data with one statement like delete from table where ...
or in small chunks, eg 2000 records a once?

I assume you delete in one statement:

just a few comments:

*) for about 25% of the data a full table scan might be better, I can´t imagine that the index is the best choice in your scenario -- try to update the statistics

*) such a big data change stresses the transaction log, can you set the db in simple mode during this operation?