Re: Embedded SQL/Index/Speed/How

[Michael Ryan <michaelrtr@xxxxxxxxx>]

Thanks Charles...good information...I'll check it out.

On Wed, Mar 23, 2011 at 12:58 PM, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
> There's a system wide SQL plan cache...you can see it by right
> clicking on database in iNav....
>
> I suppose it's possible that having the index changed the plan in the
> cache even if the index wasn't directly used...
>
> When you say it ran "dog slow", did you run it more than once?
>
> It's possible the system created a temporary index index at some
> point, which I don't believe is necessarily deleted automatically when
> a matching permanent index is created.  Thus: it's possible the
> temporary index is still around....
>
> What did VE show you on the "dog slow" runs vs. now?
>
> Honestly, it's pretty much impossible to say what happen based upon
> what you've told us.
>
> You might see if you can still find the "dog slow" runs in the plan
> cache...there's an analyze function that might tell you more..
>
> Lastly, consider taking SQL plan cache snapshots before you start
> messing with stuff...then have something to compare to after you've
> seen a change in behavior.
>
> For more info:
> http://systeminetwork.com/node/26693
> http://systeminetwork.com/article/5-essential-ways-use-iseries-navigator-sql-plan-cache
>
> HTH,
> Charles
>
>
>
> On Wed, Mar 23, 2011 at 12:32 PM, Michael Ryan <michaelrtr@xxxxxxxxx> 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.
> --
> 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.