Tam,

If your using ER then you'll have to worry about the 5000 record threshold that causes transactions to fall into the stable queue. The rollback time is something that you may want to consider, i.e. it would take longer to rollback a 10000 record transaction than it would to rollback a 1000record transaction. Be aware of the transaction log space that will be used be each transaction. Don't use to much or you'll risk a long transaction.You would have the same concern if you perform the delete during a busy time when lots of other transaction are causing the logs to roll rather frequently.

Lock the table(s) in exclusive mode.

If you are going to write some generic script to do the work for you, I would recommend that, then smaller transactions are almost always better. If you are going to buildmultiple SQL files that you have to run then larger transaction will be easier to manage.

No matter what you do I would not create any transaction larger than say 5000 records simply because the larger the transaction the greater the potential to create more work for you if the run does not gosmoothly.

I have a generic perl script that I use to perform large updates/deletes on my servers. I use 500 record transactions and that works just fine but I'm sure that I could go larger with out issue if I did not have to be concerned with ER.

DL


----- Original Message ----
From: Tam OShanter <tam@oshanter.com>
To: informix-list@iiug.org
Sent: Tuesday, September 26, 2006 1:40:16 PM
Subject: Re: Massive Delete

Okay,
So I cannot do this in one transaction without killing my server so....
Given my row size, what considerations do I have when trying to decide the
optimal number of rows to delete in a given batch?

Thoughts?

Tam.

"Quman" <yquman@gmail.com> wrote in message
news:mailman.630.1159295524.20706.informix-list@iiug.org...
> Normally, we have a script(perl,...) to do this,
>
> Inside the loop, we can control the number of rows we want to delete
> in a short transaction. You can also have a couple of seconds sleep
> for next round if you like.
>
> You can juststopt(CTRL-C) it any time and restart it later.
>
> thanks,
> Quman
>
> On 9/26/06, Tam OShanter <tam@oshanter.com> wrote:
>> Hello Again Friends,
>> Thanks for the help you've provided me so far.
>>
>> So....
>> Here are some stats on a table I have:
>>
>> total recordcount 20,454,537.00
>> total record count to be moved to DW and deleted 11,000,921.00
>>
>> row size of 413 bytes.
>>
>> How do I go about deleting this data without rendering my server
>> unusable?
>>
>> I saw a suggestion of deleting records in 500 record batches, but that is
>> a
>> helluva lot of batches to get done what I want.
>>
>> Any other ideas?
>>
>> Thanks in advance for the advice.
>>
>> Tam.
>>
>>
>> _______________________________________________
>> Informix-list mailing list
>> Informix-list@iiug.org
>> http://www.iiug.org/mailman/listinfo/informix-list
>>


_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list