Re: SQL statement performance question

Good points, Etienne.

Under a client server architecture, you need to take into account the
transmission and conversion of the data, not just building the cursor
initially on the server.

Converting a host cursor to a JDBC formatted stream takes time.
Transmitting the data takes over a socket takes time.  After the data stream
arrives at the client, generating a result set object takes more time.

Nathan.


------------------------------

message: 6
date: Fri, 2 Apr 2004 11:01:25 -0500 
from: "Richards, Etienne (EM, PTL)" <Etienne.Richards@xxxxxxxxxx>
subject: 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.


------------------------------

_______________________________________________
This is the Java Programming on and around the iSeries / AS400 (JAVA400-L)
digest 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.



End of JAVA400-L Digest, Vol 2, Issue 123
*****************************************