MVSFORUMS.com

[selva21]

Hi,

I am in the process of consolidating tips on writing better SQL statements to improve the performance.

If you have something to add, please let me know.

Here are few.

Guideline for using a SELECT statement:

Specify only needed columns
Limit the number of rows by using predicates
Specify the FOR UPDATE clause if applicable
Specify the OPTIMIZED FOR n ROWS clause
Specify the FETCH FIRST n ROS ONLY clause
Specify the FOR FETCH ONLY clause if applicable
Avoid numeric data type conversion if required

Guidelines when choosing a data type

Use Character (CHAR) rather than varying-length data type for short columns
Use VARCHAR of VARGRAPHIC rather than LONG VARCHAR or LONG VARGRAPHIC.
Use Integer (SMALLINT, INTEGER,BIGINT) rather than floating point number (REAL or DOUBLE) or decimal (DECIMAL ) if you do not need to have the fraction part.
Use date-time (DATE,TIME,TIMESTAMP) rather than character (CHAR).
Use numeric data types rather than character.


Thanks and Regards,
A.Selvakumaran.
_________________
--------------------------------------------------------
Knowledge is power. But Imagination is more important than Knowledge. -Albert Einstein
--------------------------------------------------------

[kolusu]

Selva,

Please search before posting. This topic had already been discussed. check this link

http://www.mvsforums.com/helpboards/viewtopic.php?p=2799

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

[selva21]

Thanks Kolusu !
_________________
--------------------------------------------------------
Knowledge is power. But Imagination is more important than Knowledge. -Albert Einstein
--------------------------------------------------------

[bauer]

refer to IBM manuals, check the chapters for then EXPLAIN Command, you will find some additional tips.