Re: Embedded SQL/Index/Speed/How

On 3/23/11 10:38 AM, Mark S. Waterbury wrote:

<<SNIP>>
The only way I know of to control this, to some extent, for
"benchmark" purposes, is to create a separate storage pool and use
the SETOBJACC command to "bring" the entire *FILE into the storage
pool, then run your query, if you have multiple indexes (or LFs) over
the base table, you may also want to issue SETOBJACC for those
objects, too. Then, at least you can see "consistent" results of the
actual performance of the query itself, taking the system paging out
of the equation.

For simple\quick testing, I often prefer instead to /purge/ the objects from memory before each test\query to get more consistent results across those tests; forcing the system paging to be part of the equation in each test.

SETOBJACC DBFname *FILE POOL(*PURGE) MBR(*FIRST) MBRDATA(*BOTH)
Member MBRname purged from main storage.

Note: since the *FILE and *MEM of the LF is of little interest to the query, unless named by the query, the MBRDATA(*BOTH) [the default; versus *ACCPTH or *DATA] accomplishes bringing or purging the access paths over the data in the one SETOBJACC request versus tracking down each LF by something like DSPDBR and issuing the request for each.

Regards, Chuck