OE10.2B08 HP-UX 64i
I have about 1.7M records to load in a TT. It takes about 14 minutes to fill the TT with a Fill() or for each with buffer-copy. That's about 2K recs per second.
Reading the 1.7M recs table takes 30 seconds.
I can speed it up by not copying the fields I don't need. But I'd rather not do this.
This seems "slow" to me, but I could be wrong. My -T is pointing to a good hard disk, is there anything I should look at to improve the fill performance of a temp-table ?
Take a look at the -Bt (# TT buffers) and see if bumping that up from default helps.
tmpbsize is 8 already, but the -Bt is not increased, as this is a one time process (fill once, do work and exit). I just got a new drive on the server will see if that makes a difference.
> fill once, do work and exit
If you are going to exit the session after doing the work then it isn't going to hold onto temp-table buffer memory indefinitely.
Why not increase -Bt so the temp-table can be memory-resident? It should improve performance significantly if you eliminate the disk I/O, more so than moving disk I/O to a better disk.
Or put -T (and the DBI files) on a RAM disk.
Walking the whole table after fill takes less than a minute, not a problem really. Also, I don't care if a percentage of the table is in memory when reads from disk are fast.
Time, I have not thought about the RAM disk. Good idea I'll check with the datacenter if this can be done without too much cost.