Re: SQL statement performance question

Dieter,

You are right that it is usually to do it right and then make it fast.
There are so many factors that go into iSeries Java performance this
that it is rare to find a rule that doesn't have some exception.

New iSeries hardware is getting faster and I can say from direct
experience that what used to be lower-end is now capable of supporting
high volume Web sites. The classic cases for select a,b from tabx vs.
select * from tabx are when you are just retrieving a description like
customer name and in complex joins where you generally pull limited data
from each joined table. In most cases this will run faster and be worth
the effort (yes I have benchmarked both ways).

David Morris


>>> dieter.bender@xxxxxxxxxxxx 4/3/2004 2:39:10 AM >>>
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....

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

...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