Re: Embedded SQL/Index/Speed/How

[Michael Ryan <michaelrtr@xxxxxxxxx>]

Good point Mark...thanks for that. Give me enough memory and
everything runs fast! :)

On Wed, Mar 23, 2011 at 1:38 PM, Mark S. Waterbury
<mark.s.waterbury@xxxxxxxxxxxxx> wrote:
> 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.
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.