for each table a table-scan no-lock:
create b.
buffer-copy a to b.
end.
I would expect the same amounts for Reads in a as Creates in b but I have a double read rate. (used protop)
Hi Stefan ,
I analysed from my procedure it reading data as single only. can you verify with these query.
define buffer bcust for customer.
define temp-table tcust like customer.
for each bcust no-lock:
create tcust.
buffer-copy bcust to tcust.
end.
for each tcust no-lock:
display "Num:" tcust.custnum "Name:" tcust.name.
end.
If you are not ok with these . Eloborate briefly your query.
Hmm, when I use TABLE-SCAN protop shows READS twice, without the same read/create amounts.
I just hit another problem using this code, the deletion was dramatically slow.
outer: do while true transaction:
iOuter = 0.
for each X00_BOOKING table-scan exclusive-lock:
iOuter = iOuter + 1.
delete X00_BOOKING.
if iOuter = 100 then
next outer.
end.
leave outer.
end.
After I changed it to
for each X00_BOOKING exclusive-lock:
delete X00_BOOKING
end.
.. it was 10 times faster.
This table is in an AREA II.
Doc:
TABLE-SCAN provides the most efficient access method when retrieving all rows of a temp-table or a database table in a Type II Storage area. TABLE-SCAN returns these rows without using an index. When the TABLE-SCAN keyword is used, the AVM only accesses the record block instead of both record and index blocks.
Are you looking at overall record reads? Or reads shown in "table activity"?
New problem... new thread?
In any event... yes, your DELETE code will be slow if you do it like that. "Chunking" multiple operations into a larger transaction is good for CREATE and UPDATE. It is bad for DELETE and irrelevant for READ. The best DELETE performance is a single thread with one DELETE per transaction.
> I just hit another problem using this code, the deletion was dramatically slow.
TABLE-SCAN reads all RM blocks owned by table. DELETE statement does not release RM blocks. RM blocks without records will be kept on RM chain (btw, the bocks with most recent deletes will be most likely added to the chain's bottom). Your "outer" query will read these blocks over and over.
ChuiMonster,
Can you provide more information about why chunking is bad for DELETE operations? We have seen signficant performance improvements using chunking v individual transactions in some cleanup scripts (in some cases 12h down to 4h). Environment was single-user w/ the database configured for AI and OE replication.
"Why" is really more of a question for Rich and company ;) But, speculatively, it seems to me to have at least a little bit of something to do with "placeholders" in the index blocks. George's comments about the RM chain also seem relevant.
If your cleanup is a combination of updates and deletes then chunking may still help you. But if it is pure deletes my testing has convinced me that a single thread doing one record at a time is fastest.
Of course I love being wrong about these things -- it means that I have learned something and the day has not been wasted. So if you have some test data that shows otherwise please share it!
Are you looking at overall record reads? Or reads shown in "table activity"?
I am looking at Table Activity, read with table-scan has the double value than create.
Over the night I got the idea that it could be normal because the index read replacement is now moved to the table itself, but this is a little bit confusing because you need to know that reads are twice for some programs using table-scan.
Otherwise you are wondering about more reads than you have records in it.
For chunking:
I am nut sure but I know that the code was working fast, then I tried to delete the table with an index to analyze speed and since it's slow. Sounds crazy, I know ...
BTW: How nice it would be to get a full lock on a table, especially for aggregation tables, do run something like a CLEAN table command instead of waiting for deleting row by row. But this was discussed so often without a good solution.
... a CLEAN table could have another advantage: The delete trigger could to be loaded and cached during processing this command to reduce I/O if -q or a lib isn't used.