Re: sql performance question

["rick baird" <rick.baird@xxxxxxxxx>]

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.