Hi all!
I'm looking for some hints and tips on how to construct "nice" cleanup procedures.
In out application, we have various log/transaction tables, which can grow to be quite large. The data in these tables are often obsolete after a period of time, and should be deleted.
The problem ,as I observe it, is that deleting row is very resource consuming. Deleting 100-200.000 rows will in practice stop every other program.
Am I missing something?
I've tried deleting every row in a single transaction, and experimented with various batch-sizes.
As far as I can tell, Progress is only capable of deleting 50 rows per second, which is far, far to little.
Progress Denmark have indicated, that this is true, and that deletions is not optimised in the kernel.
Recently I've looked into the setup of the database. I've tuned the Bi block- and cluster size and added 2 apw's. The database itself is doing a lot better now, but I still can't delete more that about 50 rows/sec.
Any help will be greatly appreciated.
Regards
Mike
I'm looking for some hints and tips on how to construct "nice" cleanup procedures.
In out application, we have various log/transaction tables, which can grow to be quite large. The data in these tables are often obsolete after a period of time, and should be deleted.
The problem ,as I observe it, is that deleting row is very resource consuming. Deleting 100-200.000 rows will in practice stop every other program.
Am I missing something?
I've tried deleting every row in a single transaction, and experimented with various batch-sizes.
As far as I can tell, Progress is only capable of deleting 50 rows per second, which is far, far to little.
Progress Denmark have indicated, that this is true, and that deletions is not optimised in the kernel.
Recently I've looked into the setup of the database. I've tuned the Bi block- and cluster size and added 2 apw's. The database itself is doing a lot better now, but I still can't delete more that about 50 rows/sec.
Any help will be greatly appreciated.
Regards
Mike