Re: sql performance question -- MIDRANGE-L

Same exact messages in both runs, same access paths chosen, everything.

hmmmm. very strange.

Oh well, thanks to all who answered, but it's not today's problem -
back to work.

On Tue, Mar 18, 2008 at 3:38 PM, Bradley V. Stone <bvstone@xxxxxxxxxxxx> wrote:

Do a STRDBG, run em both again, then look at the job log. It should tell
you what it's doing as far as building/reusing access paths, etc.

Bradley V. Stone
BVSTools - www.bvstools.com
eRPG SDK - www.erpgsdk.com

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of rick baird
> Sent: Tuesday, March 18, 2008 2:35 PM
> To: Midrange Systems Technical Discussion
> Subject: Re: sql performance question
>
>
> Elvis,
>
> That's what I thought too. neither query will be in run in
> production, I'm just running them in STRSQL while doing a little data
> integrity research.
>
> It just really surprised me that there would be a difference in
> performance between the two.
>
>
> On Tue, Mar 18, 2008 at 3:26 PM, Elvis Budimlic
> <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> > There is no valid reason that I'm aware of.
> > In fact, I think that latter statement should run slightly faster.
> >
> > I'm guessing what happened is probably that access plan built
> by the query
> > optimizer differs for the two statements and it's saved
> somewhere (program,
> > package, plan cache), so you're best off forcing a one-time access plan
> > rebuild for the latter statement (there is a QAQQINI option to
> effect this).
> >
> > Other than that, I'd suggest contacting IBM and reporting this
> little oddity
> > as a defect.
> >
> > HTH, Elvis
> >
> > Celebrating 11-Years of SQL Performance Excellence on IBM
> i5/OS and OS/400
> > www.centerfieldtechnology.com
> >
> >
> >
> >
> > -----Original Message-----
> > Subject: sql performance question
> >
> > Why would the last of the following two SQL statements run ten times
> > slower than the other?
> >
> >
> > SELECT *
> > FROM BLORDLP A
> > JOIN BLORDDP B
> > ON ODORD# = OLORD#
> > AND ODLINE = OLLINE
> > WHERE OLSTTS <> ODSTTS
> >
> >
> > SELECT ODORD#, ODLINE, ODSTTS, OLSTTS
> > FROM BLORDLP A
> > JOIN BLORDDP B
> > ON ODORD# = OLORD#
> > AND ODLINE = OLLINE
> > WHERE OLSTTS <> ODSTTS
> >
> > The only difference being the first selects all fields and runs
> > immediately, the second only selects 4 fields and takes forever to
> > run.
> >
> > thanks,
> >
> > rick
> >
> > --
> > 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.
>

--

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.