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 thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].