RE: sql performance question

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

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