SQL Best Practices - Part1

Never put SQL scalar functions on columns in the WHERE predicates.
For example: WHERE YEAR(HIREDATE) = 2009 should be recoded as WHERE HIREDATE BETWEEN '2009-01-01'and '2009-12-31'.
When placing SQL scalar functions on columns in the SELECT portion of the SQL statement does incur some minimal overhead, but applying it to a column in the WHERE clause causes the predicate to become stage to non indexable.

Same applies for mathematics.
For example: WHERE HIREDATE – 7 DAYS > :HV-DATE should be recoded as WHERE HIREDATE > :HV_DATE + 7 days. Having the mathematics on the host variable is not a problem.


Code only the columns needed in the SQL SELECT. Having extra columns can have an effect on the optimizer's choice of index only processing, larger sort costs, and at times the join type chosen.

Watch out for any ORDER BY, GROUP BY, DISTINCT, UNION, INTERSECT, and EXCEPT. These may cause sorts to occur in the processing. Make sure they are truly needed in the query.

Minimize SQL requests from your application program. This is especially true in batch processing where the number of statements executed is typically high. Every time a SQL request is sent to DB, there is overhead involved because it has to be sent from one address space in the operating system (for example Cobol or Java™) to the DB address space. In general, developers should minimize these requests by reducing the number of times they open and close cursors, execute select statements, and so on.

All developers should know how to read the basics. For example:
Is there indexing being used or a table space scan?
Which index is chosen, and what are the matching columns?
Are there any sorts going on? Why? What is the number of rows going into the sort?
Are there any Stage 2 predicates?


More on the WAY......