Re: Embedded SQL/Index/Speed/How

Michael:

Also bear in mind that OS/400 and i5/OS is a pure paging system. That is, real main storage is a cache for objects in single-level storage (that reside permanently on DASD). The first time you run your query, those pages containing the needed data (rows) are brought into real main storage memory, and they will remain there until something happens that forces those pages to get paged out or "stolen" for other purposes.

So, even without an index or a plan cache, you can still see what you observed, and that query can seem to run much faster the second and subsequent times.

Also, with more modern IBM i system models, the real main storage sizes are much larger than just a few years ago, so it is even more likely that those pages can remain in main storage for much longer periods before getting replaced ... :-o

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.



> On 3/23/2011 12:32 PM, Michael Ryan wrote:

Ok...a co-worker had a program with an embedded SQL statement,
selecting records with a number of different criteria. It ran dog
slow. I suggested creating an index to remove some of the 'fixed'
criteria. Did that, recreated the program, ran like the wind. Then we
decided to test things out. Deleted the index. Still ran fast. Used an
old copy of the program that was created before the index was made.
Still ran fast. Deleted the index and the program, recreated the
program. Still ran fast.

What happened? Was an access plan created and stored someplace? I
would think deleting the program and index and recreating the program
would remove any consideration of an access path.

Thanks for any thoughts.