batch_begin/end and pack()

[CALocklin]

Can anybody (Alpha?) give me a definitive answer....

If I run an xbasic <tbl>.batch_begin() operation and end it with <tbl>.pack(), is there any reason to run <tbl>.batch_end()?

Actually, this could apply to any A5 version that has the batch_begin() command - v5 and later maybe.

Here's why the question:

I'm running a routine to archive some old records (actually 4 tables of them but based on the date in what would be the parent table of the set if they were in a set) and it runs MUCH faster when batch_begin() is used because there will often be many thousands of records archived at a time. As each record is copied out to the archive, it is also deleted. When the batch_end() runs, it seems to just update the indexes. If a Pack() operation is run before (or after) the batch_end(), the Pack() operation also updates the indexes. Consequently, running the Pack() operation and skipping the batch_end() seems to be adequate. If I DO run a batch_end() after the Pack() then the indexes are updated twice. Since two of the tables have 8-10 indexes and typically over 200,000 records, it takes a quite awhile.

My testing so far seems to indicate that it is NOT necessary to run the batch_end() after running the Pack() but, since I can't see "under the hood", I'm not absolutely positive. Since there are about 8 different companies using this application, I'd like to avoid any hidden problems.

[Stan Mathews]

Re: batch_begin/end and pack()

Cal,

My take on .batch_begin() and .batch_end() is that they merely prevent outside "interference" with a table or set while changes or enters are taking place.

..... Alpha Five optimizes data entry operations by either opening the table exclusively or by preventing write access to the file by other users.

It would seem that the .batch_end() command is available only for consistency and/or if it is necessary/desirable to turn off that protection later in a script. As such I don't believe that .batch_end() is necessary.

Not definitive, but logical?

[CALocklin]

Re: batch_begin/end and pack()

"Normally" the batch_end() is required - if the batch isn't "officially ended" the indexes aren't updated when the table is closed. I found this out by forgetting to run batch_end() once.

While the "batch" is running and a table is being modified, the indexes are NOT being updated. This is part of the reason it is so much faster. When the batch_end() runs, the indexes are updated. As far as I know, this is all that happens with batch_end() other than the table is also being released for other access.

My hope is that a
tp.pack()
tp.close()
does the same thing so the system doesn't have to force another index update. So far everything seems to be OK but I was hoping for some confirmation from Alpha or someone who had been doing it for awhile without problems.

FWIW: From what I've seen, batch processing can make a huge difference in time (10-20 times) when a large number of records are being modified but it has little effect when adding new records. However, the "crossover point" for the optimum number or records to edit without the batch and with the batch is usually a matter of trial and error because it depends on the size of the table and the definitions for field rules and indexes. Certainly, I wouldn't bother if I was processing anything under about 40-50 records on a large table. And, on really small tables (a few hundred records?), it probably isn't even worth considering. On the other hand, adding the commands only takes a few seconds if it helps.

One warning: Adding the batch_begin/end command does not automatically result in a batch operation. If someone else is using the table and your script cannot get exclusive access, the batch command is ignored. Therefore it is a good idea to get exclusive access before running the batch_begin() command. The reason I mention this is because initial testing indicates that the routine I'm working on now would probably take days to complete if someone decided to archive a years worth of data without it running as a batch operation. As a batch operation it processes about 20 records per second. With somewhere around 200,000 records per year being entered (in a total of 4 tables), that means it would take about 3 hours to archive a year's worth of data and delete those same records from the original 4 tables (one parent and 3 child tables).

And, yes, based on the time required - which I just discovered a few minutes ago - I'm trying to think of a more efficient way to do this than copying the records to a new table. The problem is that this routine needs to put all archived records into one set of tables - each year (or month or whatever) would be added to the last archive rather than creating a new archive for each period. Now, if I could only convince them that it's ok to just delete all records older than xx years....

All this work just to save them time when updating indexes and running backups. It may not be worth it.