RE: sql performance question

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