Re: SQL statement performance question

    Hi Keith,

    Actually, just about everything I've ever read, across databases,
client-server processing or not, has indicated that specifying columns is,
in general, more efficient tham using SELECT *.

    I delayed my response a bit to do some research.  I though that possibly
DB2 did something different or that there was a recent change.  Here are a
few results that favor specific columns.  While I don't claim to be
comprehensive, I didn't see anything in favor of SELECT *.

"Top Ten SQL Performance Tips"
http://www.dlt.com/quest/pdf/database%20management/db2/Top%20Ten%20SQL.pdf

"Coding DB2 SQL for Performance: The Basics"

<www.ibm.com/developerworks/db2/library/techarticle/0210mullins/0210mullins.
html>

and from "DB2 UDB for iSeries Database Performance and Query Optimization
V5R2"

<http://publib.boulder.ibm.com/iseries/v5r2/ic2924/info/rzajq/rzajqmstselect
opt.htm#HDRSELECTOPT>

    So, I know I'm putting you on the spot, but it is not to make you look
bad.  I just assume you have a basis for your response and wonder where you
found the information.  I'm always more than happy to change my thoughts if
there is a factual basis.


                                                  Joe Sam

Joe Sam Shirah -        http://www.conceptgo.com
conceptGO         -        Consulting/Development/Outsourcing
Java Filter Forum:       http://www.ibm.com/developerworks/java/
Just the JDBC FAQs: http://www.jguru.com/faq/JDBC
Going International?    http://www.jguru.com/faq/I18N
Que Java400?             http://www.jguru.com/faq/Java400


----- Original Message -----
From: "Keith McCully" <Keith.McCully@xxxxxxxxxxx>
To: "Java Programming on and around the iSeries / AS400"
<java400-l@xxxxxxxxxxxx>
Sent: Friday, April 02, 2004 10:28 AM
Subject: RE: SQL statement performance question


> SELECT * FROM mytable will always be faster than SELECT A, B, C FROM
mytable with all else being equal although, in practice, the difference may
not be noticed.
>
> Basically the first statement is a reference to a set of rows from an
existing table.
> The second statement requires a new table to be built, even if mytable
only contains fields A, B & C.
>
> Keith
> >-----Original Message-----
> >From: java400-l-bounces@xxxxxxxxxxxx
> >Subject: Re: SQL statement performance question
> >
> >My experience with SQL is that its completely unpredictable.  The only
way
> >to be 100% sure which query is faster on your iSeries configuration
> >retrieving from that particular table is to write some code to run each
> >query 100 or 1000 times and see which one is fastest, although in the
case
> >of just picking which fields you want from the same table I doubt you'll
> >notice any difference.
>
> >Nigel.
>
> >-----Original Message-----
> >Hi
> >which SQL statement is more efficient
>
> >SELECT * FROM mytable
>
> >  OR
> >SELECT A, B, C FROM mytable
>
> >Why is it more efficient,
> >If there are any articles about writing efficient SQL
> >querries will really help
>
>
> >Ashish
>
>