RE: SQL statement performance question

One thing to remember is that 
        select * from mytable

will require the column meta data of all columns in mytable to be returned
to the client as well as the actual value of all columns. This actually has
a measurable impact when looking at jdbc. The more columns mytable has the
more meta data and column data will be returned to the client.

In some cases where you have a table with many columns and you only have a
few rows returned, you will see more meta data being returned than actual
data. When you have 100's of connections you have a large combined impact
(network, jvm, AS/400, etc.).

If you were to use prepared statements and you have a prepared statement
cache then you might not notice it (but this is another topic).

When you look at the overall impact of this on jdbc you might not see a
measurable difference for just one connection on a machine that is not under
load. Consider the following that is involved (java):
1. All column meta data will be returned
2. All column data will be returned for each row.
3. #1 and #2 will impact how much needs to be transmitted 
   from the AS/400 to the JDBC connection.
4. JDBC connection needs to process #1 and #2 even if you do not 
   use the column.
5. All character data will be converted from EBCDIC to UNICODE
   (Meta data and column data)
6. Index Only access will not be very likely.
7. #3 and #4 will increase heap usage of the JVM.

Well, I think you might get the picture. The cumulative impact needs to be
consider.

Etienne

-----Original Message-----
From: Keith McCully [mailto:Keith.McCully@xxxxxxxxxxx]
Sent: Friday, April 02, 2004 10:29 AM
To: Java Programming on and around the iSeries / AS400
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



_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
mailing list
To post a message email: JAVA400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/java400-l
or email: JAVA400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/java400-l.