Re: SQL statement performance question

Hi Joe,

first of all: performance is better, if you have a measurment to prove it and 
there are a lot of fairy tales out there how to code fast. Some of them:

- fastest is to put the appserver and database to same box: in most cases its 
getting faster if you split both and put the appserver to a none as400 box 
even nt might be faster. The above might be true for big as400 with multiple 
processor, plenty of memory and a lot of fast dasd only.

- the native driver is faster than the toolbox driver: in my experience the 
compiled toolbox driver is as fast as the native driver.

- CRTJVAPGM makes it faster: only true for the toolbox driver, not for typical 
java apps

- lower isolation level is faster: seems not to be true for as400, in my 
experience it doesn't matter (only serializable might be slow)

- batch updates are fast: seems to me not to be implemented in the driver, 
might be even slower.

- SELECT * is slower/faster than select a, b, c ...: never saw a diffrence in 
real life.

Whats the conclusion???

First do it right and then make it fast. Most important thing is a well 
modularized design following proven patterns and built a stable and easy to 
maintain application. If you don't match performance requirements or 
scalability requirements at the first shot, its quite normal! Now begin your 
analysis of your application, use the database monitor and load test 
utilities to make a profile of your application and to understand wich 
components slow down your response times and limit your scalability and 
enhance these components until you meet the requirements.

BTW: I never found the problems in using select * instead of select a, b, c, 
or using String instead of StringBuffer, or using the Driver A instead of 
Driver B, or using the "wrong" optimisation...
Problems I found have been: locking conflicts, reading hundred of thousands of 
records in one user transaction, synchronisation issues (JNI rpg calls etc.) 
with even deadlocks, sending Megabytes of data to the browser (Combo boxes 
etc), poor design of application (making hundreds of non database server 
requests, mix of java rpg issues again), reading the same data over and over 
again, caching data never used again, ....


Dieter Bender

On Freitag, 2. April 2004 22:38, Joe Sam Shirah wrote:
>     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/rzajqmstselec
>t 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
>
> _______________________________________________
> 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.

-- 
mfG

Dieter Bender


DV-Beratung Dieter Bender
Wetzlarerstr. 25
35435 Wettenberg
Tel. +49 641 9805855
Fax +49 641 9805856
www.bender-dv.de
eMail dieter.bender@xxxxxxxxxxxx