Ann W. Harrison wrote:

>
>
> Erik,
>>
>> Currently I'm using
>> SortMemBlockSize = 1048576 (Default)
>> SortMemUpperLimit = 1073741824 (1G)
>>
>> It didn't seem to me like increasing the SortMemBlockSize should make
>> much of a performance difference by reading the description, but perhaps
>> I'm wrong?
>
> The SortMemBlockSize limits the length of a sorted run ... and I think
> that when a run gets to that size, it's written to disk. The upper
> limit is just for merging the runs (I think).
>

OK, I will definitely try increasing SortMemBlockSize in that case.

>> I'm still getting fairly slow write performance however... I would hope
>> 5 discs in raid-0 could update 4.5 million records in less than around
>> 10 minutes, but apparently not.
>>
>
> Can you tell us more about the write operations?

The table looks like this, with a few more address related fields.

CREATE TABLE ADDRESSDB (
ADDRESS_ID INTEGER NOT NULL PRIMARY KEY,
....
ZIP_CODE CHAR(5),
ADDON CHAR(4),
CARR_RT CHAR(4),
STATUS INTEGER,
STATUS_DATE TIMESTAMP,
)

Just doing update ADDRESSDB SET WS=NULL takes several minutes to complete.

With an 8k page size, doing SELECT DISTINCT ZIP_CODE FROM ADDRESSDB;
takes about 140 seconds. Bumping page size to 16k (gbak reports 8k as
the maximum by the way) cuts the SELECT DISTINCT ZIP_CODE FROM ADDRESSDB
time down to around 15 seconds.

Seems to me as if allowing page_size to be tuneable up past 128k or so
might be a good idea.

I was able to get my 'walking the list and updating' procedure runtime
under 60 minutes by doing removing any unnecessary updates and only
sorting one zip code at a time instead of trying to walk the whole
database at once. I must say that optimizing for a database which won't
fit in RAM as a very different case than optimizing for one which can.

Thanks

--erik