Query Could not Create Temporary File

[Mike Thomson]

I have a script which rebuilds the balances on a file called "Bookings" from 2 related tables , being "Charges" and "Payments". The script loops through all bookings and then via related records in the "charges" and "Payments" table.

The script will work for around about 16 records and then I get the message:-

idx1.query_create() could not create temporary file

I have read about problems in relation to the number of queries open , but as you will see from the code I frop each query after each sub - loop. Interesting If I remove one of the sub loop s(eg: idx2 query loop) . It runs to completion.

Can anyone help?


'*** rebuild Account Balances ****
t=table.open ("Bookings")
t1=table.open ("Charges")
t2=table.open ("Payments")
t.index_primary_put("Bookno")

t.fetch_first()
while .not. t.fetch_eof()
'*** Update Status Bar ****
nCount=nCount +1
statusbar.Percent(nCount,nMax)

'**** Get Charges *****
nCharges=0
nChargeTax=0
nPayments=0
query.order = "bookno"
query.filter = "bookno = " + str(t.bookno)
idx1=t1.query_create()
t1.fetch_first()
while .not. t1.fetch_eof()
nCharges=nCharges+t1.Amount
nChargeTax=nChargeTax+t1.tax
t1.fetch_next()
end while
idx1.drop()

'*** Payments ****
query.order="bookno"
query.filter = "bookno=" + str(t.bookno)
idx2=t2.query_create()
t2.fetch_first()
while .not. t2.fetch_eof()
nPayments=nPayments+t2.amount
t2.fetch_next()
end while
idx2.drop()

t.change_begin(.t.)
t.charges = nCharges
t.Chargestax = nChargeTax
t.Payments = nPayments
t.change_end(.t.)
t.fetch_next()

end while
t.Recalc_CalcFields()

t2.close()
t1.close()
t.close()

[Tom Cone Jr]

RE: Query Could not Create Temporary File

Mike, I think you've identified the problem. Three ideas come quickly to mind:

1) Use dbsum() or tablesum() to accumulate the "Charges" and "Payments" instead of the two inner loops you're doing.

2) If you want to retain the current code give the system a chance to catch up to the script by pausing the script before fetching the next Bookings record. Xbasic_wait_for_idle() will do this.

3) If I were not going to use dbsum() or tablesum() I'd avoid repeatedly filtering the Charges and Payments tables by simply doing a more direct accumulation sequence like this:

a) set index to the booking number field
b) fetch the first matching Charge (or Payment) record
c) begin accumulating the value
d) fetch the next record and accumulate it if it's the same booking number, or stop if it's different.
e) continue until the current booking number doesn't match any longer

-- tom

[Mike Thomson]

RE: Query Could not Create Temporary File

Thanks Tom,

xbasic_wait_for_idle() certainly works and it also makes the code much solower to run.

I would have thought that in a sequential script (that I beleive runs in a single thread) , that there would be no need for this command. It's a bit hit and miss as to where to use it - the danger of course being (as per this example) - the problem dosen't hit until sometime after you have completed your testing.

I will look at dbsum & tablesum() and see how they can help me. I guess I have written this using the same type of coding logic I have used for different database systems.

Thanks again for your help

Cheers

Mike

[Tom Cone Jr]

RE: Query Could not Create Temporary File

Mike,

I enjoyed reading your code. It's nice and clean. Was easy to follow. However, as I think you'll find, dbsum() and tablesum() encapsulate the same functionality. dbSum() will run faster (than Tablesum()) but will require the presence a permanent index.

Let us know what you finally decide.

-- tom

ps. When to employ xbasic_wait_for_idle() is at first something of a hit or miss experiment. As you gain experience you'll begin to think of it whenever your scripts set a process in motion that requires disk activity. In the case at hand each query you run results in a query list file that must be written to disk. The query list is not maintained in memory. It's this list which then is used to step through the selected records to aggregate your total Charge (or Payment). Anyway, while the disk write activity is occurring the script keeps running. It does not wait for some signal from the operating system that the disk write is finished. Hope this helps.

[Mike Thomson]

RE: Query Could not Create Temporary File

Hi Tom,

I used dbsum as suggested
On my data it ran in 2 seconds vs 5 seconds for the script I had with xbasic_wait_for_idle.

It also had the advantage that the Status bar is smooth rather than jerky. (I dont know why alpha does this - since I am only updatin it exactly the same number of times and in the same loop)1

This function is fine when you only want to sum a single field, however if you have more than one field (eg: in My example tax and amount in the payments table) you have to run it twice - which means 2 passes of the database for the same filter.

[Tom Cone Jr]

RE: Query Could not Create Temporary File

Mike, yes, the double pass through the table would trouble me, too. The third approach I discussed early neatly avoids the problem and would run quite quickly.

-- tom

[Mike Thomson]

RE: Query Could not Create Temporary File

I take it what you are saying still involves using idx_create() for each of the charges and payments file, but then to loop through them individually (but once only) and then for each change do a fetch_find on the bookings table and then update records.

This will work, but from a purist point of view I would rather start with the parent a drill down to the child records since this is makes the code eaiser to follow when it comes to maintaining it.

[Tom Cone Jr]

RE: Query Could not Create Temporary File

Mike,

Actually it's a lot simpler than that.

If one assumes that the Charges and Payments tables both have permanent indexes defined on their bookno fields you can "sort" their records (so to speak) by setting their respective "bookno" indexes primary. Check the helps for information on "tbl".index_primary_put().

So, without running any queries or using dbsum() you can aggregate the Charges and Tax for any given Bookno like this:

dim vn_Charges as N
dim vn_Tax as N
dim vn_BookNo as N

Bkg_tbl = table.open("Bookings")
Bkg_tbl.index_primary_put("ByBookNo")
Bkg_tbl.fetch_first()

vn_BookNo = Bkg_tbl.Bookno

Chrg_tbl = table.open("Charges")
Chrg_tbl.index_primary_put("ByBookNo")
Chrg_tbl.fetch_first()

while .not. Bkg_tbl.fetch_eof()
vn_Charges = 0
vn_Tax = 0

result = Chrg_tbl.fetch_find(vn_BookNo)

while Chrg_tbl.bookno = vn_BookNo .AND. .NOT. Chrg_tbl.fetch_eof()
vn_Charges = vn_Charges + Chrg_tbl.Charges
vn_Tax = vn_tax + Chrg_tbl.tax
Chrg_tbl.fetch_next()
end while 'no more charges to process

'at end of the loop vn_charges and vn_Tax contain the
'total charges and tax for all matching Bookno records.

Bk_tbl.change_begin()
Bk_tbl.charges = vn_charges
Bk_tbl.tax = vn_tax
Bk_tbl.change_end(.t.)

Bk_tbl.fetch_next() 'go get next booking number
End While 'no more bookings to process

'cleanup on way out
Bkg_tbl.close()
Chrg_tbl.close()
end

Since the ordering arrangement for the table is controlled by the permanent index your script simply has to find the first matching record and then step through the records one after the other until the search key no longer matches or you run out of records. Very fast. You only open the table once. You do one find by key. You then process, fetch_next, process, fetch_next, process... and so on, until there are no more to process.

This approach is more difficult to script than a one line dbsum() statement, so I probably wouldn't use it if I were aggregating the charges and tax for a single booking. (I'm just showing the bare bones so you can see the logic. In practice you'd want to trap the errors that would occur if the Charges table couldn't be opened or if the fetch_find failed.) But in a case like yours where you're going to be compiling aggregate values for a series of bookings I'd probably use a sequence like this instead of dbsum().

-- tom

[Mike Thomson]

RE: Query Could not Create Temporary File

Or I guess I could just use my normal coding style and use fetch_find() to start each loop and then using primary index as you have suggested

ie: as follows

*** rebuild Account Balances ****
t=table.open ("Bookings")
t1=table.open ("Charges")
t2=table.open ("Payments")
t.index_primary_put("Bookno")
t1.index_primary_put (Bookno")
t2.index_primary_put("Bookno")

t.fetch_first()
while .not. t.fetch_eof()
'*** Update Status Bar ****
nCount=nCount +1
statusbar.Percent(nCount,nMax)

'**** Get Charges *****
nCharges=0
nChargeTax=0
nPayments=0

t1.fetch_find(t.bookno)
while .not. t1.fetch_eof() .and. t1.bookno = t.bookno
nCharges=nCharges+t1.Amount
nChargeTax=nChargeTax+t1.tax
t1.fetch_next()
end while
idx1.drop()

'*** Payments ****
t2.fetch_find(t.bookno)
while .not. t2.fetch_eof() .and t1.bookno = t.bookno
nPayments=nPayments+t2.amount
t2.fetch_next()
end while
idx2.drop()

t.change_begin(.t.)
t.charges = nCharges
t.Chargestax = nChargeTax
t.Payments = nPayments
t.change_end(.t.)
t.fetch_next()

end while
t.Recalc_CalcFields()

t2.close()
t1.close()
t.close()


Anyway,

Thanks for the help Tom

[Tom Cone Jr]

RE: Query Could not Create Temporary File

Exactly.

-- tom

ps. you'd need to comment out the lines which drop the two queries that are no longer being used in each loop.