D
dbeavon
Guest
Thanks for the tips guys, I will take a closer look. I think I have a few extra strategies, 1 - disable as many indexes as feasible, 2 - larger transactions (100,000 rows at a time instead of one). 3 - gather rowids into temp-tables with no-lock for concurrency, then circle back and delete - using exclusive-lock - within larger transactions: I imagine that part of the problem is overhead related to so many tiny, individual transactions. (I only get about 100 commits per second (according to 3/1 Activity: Performance Indicators - thanks for pointing it out). My hope is that maybe I can increase the number of rows deleted per commit, without increasing the costs of the transaction overhead.) Initially I was a little concerned about the table-locks. I wasn't sure if the OE database essentially goes into "single user mode" while deleting. The concern was because of the following promon output: Usr:Ten Name Domain Chain # Lock Flags Tran State Tran ID ----------- 66 0 TAB 9645 IX L Phase 2 FWD 517284980 ----------- 66 0 TAB 9648 IX L Phase 2 FWD 517284980 ----------- Notice the TAB locks that are taken with "IX" lock flags. It turns out this shouldn't be a conflict between two ABL programs that are deleting data using row locking: per KB knowledgebase.progress.com/.../P37194 Either way, I don't think OE is especially great at deleting data, at least not within ABL. It appears others have had the same questions, I found these posts while doing more research. I expect any ABL developer trying to delete data (ie. tens of thousands of rows at a time or more) would have the exact same complaints we're having. *Deleting large amount of data in openedge database* community.progress.com/.../42 *Deleting the contents of a table...fast!* community.progress.com/.../8292 Maybe the SQL92 engine has some better tricks for optimizing deletion work in batches. That will be my fallback if/when I give up on ABL. I suppose the last-ditch effort is to create a brand new storage area with duplicate tables and move in the data I want, skipping over the stuff I want "deleted". However it seems silly to do this when we are keeping the majority (eg 90%) of the rows. Thanks, David
Continue reading...
Continue reading...