Given this situation with a web application and some analytics done by a background process:
- An arbitrary number of identically structured tables
- Periodically, the background process anneals (unions) all of these tables into a readwrite cursor and then does several aggregate functions on that cursor (for example: Sum(thisfield), count(*), etc). Results are written out to new derivative tables with the tallied-up results.
- We know the name/path for each of the source tables. There could be several dozen. They are placed into an array that is dynamically built via adir() and few other steps.
My question is, what is the fastest, most robust, and least intrusive way to iterate the source tables and append to the cursor?
Currently, we are doing something like this (only with some error-checking and trapping that isn't shown here):
use (sourcetables[1]) again shared noupdate in 0 alias sourcetable
select * from sourcetable into cursor targetable readwrite
use in sourcetable
For iTable = 2 to nTables
append from (sourcetables[iTable])
endfor
select sum(this), avg(that) etc from targettable ....
My understanding is that APPEND FROM is as fast as anything and does not leave the source table open. Is this so? Also, I am uncertain regarding how flexible it is with respect to cooperative sharing, requring exclusive access, etc.
THanks very much.