Re: Embedded SQL/Index/Speed/How

[Michael Ryan <michaelrtr@xxxxxxxxx>]

When I look at the plan cache through Navigator, I just get a grey
window that pops up. I wonder if that's a Win 7 thing?

On Wed, Mar 23, 2011 at 1:03 PM, Michael Ryan <michaelrtr@xxxxxxxxx> wrote:
> 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.