DB2 SQL Practice for tuning database performance

1) Avoid distinct where ever possible. Check whether distinct is
required or not. No distinct when PK or UK are retrieved.

2) One can consider usage of union where OR condition exits &
eliminate distincts.

3) Conditions which are likely to fail should be kept first in a set
of conditions separated by AND.

4) Always use aliases.

5) Do not involve columns in an expression.
select * from emp where salary/12 >= 4000;
The query should be:
select * from emp where salary >= 4000 * 12;
i.e. Avoid using Arithmetic within SQL statements.Arithmetic in a SQL
statement will cause DB2 to avoid the use of an index.

6) Try to avoid usage of in-built or user defined functions.
select * from employee where substr(name,1,1) = 'G';
The query should be:
select * from employee where name = 'G%';

7) Avoid datatype mismatch since it may lead to implicit/explicit casting.
select * from emp where sal = '1000';
The query should be:
select * from emp where sal = 1000;

8) Substitute unnecessary group by & having with where clause.
select avg(salary) as avgsalary, dept from employee group by dept
having dept = 'information systems';
The query should be:
select avg(salary) as avgsalary, dept from employee where dept 'information systems';

Courtsey:Toolbox for IT