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
- Avoid Colum functions in WHERE clause.
- Preferable not to use NOT clause in WHERE clause.
- Use only needed columns in GROUP BY and ORDER BY since both do sorting. So, they consume more CPU time.
- Use very few WHEN conditions in the CASE statement.
- 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.
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
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;
Get new content delivered directly to your inbox.