Re: SQL statement performance question

["David Morris" <David.Morris@xxxxxxxxxxxxx>]

Ashish,

Your original message doesn't indicate whether select * or select a,b,c
returned the same data but your message implied that it does. If select
a,b,c returns limited columns there can be significant performance
gains. There are two things that factor into this. First, if the columns
selected are completely contained on an existing index, SQL will
generally not retrieve the whole record. This helps when you are
retrieving an identity column along with a description. So creating a
customerid/customer name index can be beneficial particularly for tables
containing long rows. Second, handling data takes more time in Java than
it does in SQL so selecting columns that you do not use will cause extra
objects to be created. You can minimize this by using primitive values
for things like identity columns. Accessing columns by name rather than
ordinal position is also slightly slower but I doubt it is noticeable
except in extreme cases.

For performance, you should avoid round trips to the database when your
app server is not running on the iSeries. That means scrollable cursors
are usually much slower. Running the app server close to the data can
drastically improve performance in some cases. Another area you should
watch for is how long you hold onto your objects. If you are printing a
report, it is usually best to move data directly from a result set to
your output stream. If you access data dynamically, you can also improve
performance using byte code enhancement technologies like CGLib. This
allows you to generate programs on the fly rather than using costly
reflection, instanceof, and casting techniques.

David Morris

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