DB2 5 Effective Ways to Tune SQL Queries

Here’re the ways you can tune SQL queries. SQL is the top language in all RDBMS flavors. Below is the tuning list that you can focus on.

DB2 Performance Tuning

1. Avoid Colum functions in WHERE clause.
2. Preferable not to use NOT clause in WHERE clause.
3. Use only needed columns in GROUP BY and ORDER BY since both do sorting. So, they consume more CPU time.
4. Use very few WHEN conditions in the CASE statement.
5. Code COUNT(*) proper way

Avoid Colum functions in WHERE clause

The best example is WHERE YEAR(HIRE_DATE) = 2003. You can code as below.

WHERE HIREDATE BETWEEN '2003-01-01' and '2003-12-31'

The bottom-line is avoid coding of scalar functions in WHERE clause. What happens when you code DB2 ignore index on that column.

10 Top IDMS Database Interview Questions

Preferable not to use NOT clause in WHERE clause

The best example is WHERE NOT HIREDATE > :WS-DATE. You can code as below.

Where HIREDATE <= :WS-DATE

The takeaway is especially for DATE use <= for better performance.

Use only needed columns in GROUP BY and ORDER BY since both do sorting. So, they consume more CPU time.

The best example is:

GROUP BY JOIN_DATE, COUNTRY, BASIC_SAL, EMP_ID, BLOOD_GRP. You need to use few columns. More columns means more CPU time.

GROUP BY JOIN_DATE, COUNTRY
ORDER BY JOIN_DATE, COUNTRY

Here’s more on SQL-for-data-analytics

Use very few conditions in CASE statement

Here, avoid to use more WHEN conditions.

SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, 
CASE 
 WHEN EDLEVEL < 15 THEN 'SECONDARY' 
 WHEN EDLEVEL < 19 THEN 'COLLEGE' 
 ELSE 'POST GRADUATE' 
END 
FROM EMPLOYEE

Still you want more, these you have to code in your program (not in SQL query).

Code COUNT(*) proper way

Use FETCH 1 ROW ONLY in SQL query. Also add check for SQLCODE

SELECT COUNT(*)
FROM EMPLOYEE
WHERE EMPLOYEE_ID <= 30100
AND SQLCODE = 0
FETCH 1 ROW ONLY;

Related Posts

References

• DB2 SQL For Beginners

SQL Question