SQL Performance

Patrick,

The "skinny" sql should certainly out perform the select * case.

Possible issues:

1.  It is possible, the 400 cached the sql package for the select * but not
the skinny one because it was new to the system.  If you run it  (the sql
with field1, field2) a second time you should be able to leverage system
wide sql caching support.  This happens automatically.  In additional
consideration is if you specified extended dynamic on your jdbc connection
properties.

2.  Did you read the data from the result set?  The big benefit will be as
you read the data, the skinny sql will benefit by returning less data.
NOTE:  The blocking size specified on the sql connection also factors in
here.  It defaults to 32K so perhaps in a small scenario both queries were
smaller than 32K?  Check out the jdbc driver properties for details.

3.  Is the file a simple physical file?

Paul Holm
Senior Web Architect
PlanetJ Corp.
Phone: 760-432-0600, Cell: 760-415-8830
WOW, Web Applications In Under 5 Minutes
http://www.gotWebData.net

-----Original Message-----
From: java400-l-bounces@xxxxxxxxxxxx
[mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of
java400-l-request@xxxxxxxxxxxx
Sent: Monday, August 04, 2003 12:00 PM
To: java400-l@xxxxxxxxxxxx
Subject: JAVA400-L Digest, Vol 1, Issue 768


Send JAVA400-L mailing list submissions to
        java400-l@xxxxxxxxxxxx

To subscribe or unsubscribe via the World Wide Web, visit
        http://lists.midrange.com/mailman/listinfo/java400-l
or, via email, send a message with subject or body 'help' to
        java400-l-request@xxxxxxxxxxxx

You can reach the person managing the list at
        java400-l-owner@xxxxxxxxxxxx

When replying, please edit your Subject line so it is more specific
than "Re: Contents of JAVA400-L digest..."


Today's Topics:

   1. SQL problem. (Patrick Goovaerts)
   2. Re: RAWT Exception (Patrick Galli)


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

message: 1
date: Sun, 3 Aug 2003 21:07:47 +0200
from: "Patrick Goovaerts" <pgoovaerts@xxxxxxxxxx>
subject: SQL problem.

For our webapps we use sql statements to retrieve data from our iSeries.  I
used to use the * to retrieve all fields for a record.  Thinking this over,
I realized that I regularly need only half of the fields in the database.
Because our records has mostly between 20 and 50 fields, this could be a
huge datatraffic overhead.
Therefore I tested retrieval of data using SQL as follows:
SQL1= 'select * from dbfile where field1=string'
SQL2= 'select field1, field10, field 17 from dbfile where field1=string'

surprisingly SQL2 is running slower than SQL1!  I think this has something
to do with the creation of a new temporary table with only 3 fields by the
SQL-engine, no?




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

message: 2
date: Mon, 04 Aug 2003 08:12:53 +0200
from: Patrick Galli <patrick.galli@xxxxxxxxxx>
subject: Re: RAWT Exception

David,

The headless flag  in JDK 1.4 works only on V5R2. On V5R1 NAWT shoulb be
used but unfortunately this programm runs on a V5r1 system without a
PASE environment, so NAWT is not available.
RAWT is the only option, but hangs as described on GUI client side
(windows box).

Any other hints?  Thanks, Patrick

David Morris wrote:
> Patrick,
>
> Many of the reasons for running RAWT or NAWT are eliminated with the
> 1.4
> JDK running headless. At one point you had to be on V5R2 to use the
> headless
> support but you might check to see if it works on V5R1. With headless
> you
> specify -Djava.awt.headless=true and can access many of the graphic
> functions
> that are part of AWT without a display capable server. Is that an
> option for
> you?
>
> David Morris
>
>
>>>>patrick.galli@xxxxxxxxxx 8/1/2003 5:14:19 AM >>>
>
> I'm running an application on AS400 side using RAWT, jdk1.3.1 OS400
> Rel.
> V5R1. The apllication is started in qshell
>
> The GUI Part runs on a Windows 2000 Box with jdk1.3.1
>
> The Gui Server is contacted, but then a msgbox pops up and
> following exception is displayed:
>
> java.lang.InstantiationException:
> com/ibm/rawt2/gui/java/awt/image/GBufferedImage
> at java.lang.Class.newInstance0(Native Method)
> at java.lang.Class.newInstance(Class.java:262)
> at com.ibm.rawt2.gui.GFactory.createObject(GFactory.java:415)
> at
>
com.ibm.rawt2.common.StreamEncoderDecoder.decodeArgument(StreamEncoderDecode
r.java:1363)
> at
>
com.ibm.rawt2.common.StreamEncoderDecoder.decodeParameters(StreamEncoderDeco
der.java:657)
> at
>
com.ibm.rawt2.common.RAWTChannel$ChannelListenerThread.run(RAWTChannel.java:
153)
>
>
> The appliation on as400 side terminates.
>
> any suggestions? thanks,
>
> Patrick
> _______________________________________________
> 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 1, Issue 768
*****************************************