3 Must-know Tips Before You Write SQL Query

SQL Query performance saves CPU time and cost. While reviewing SQL queries one must consider these three tips.

Checklist to Write/Review SQL Query

1. Scalar Functions

For every AVG, MIN, MAX, or SUM statement, make sure the SQL is coded to handle a null value. This will eliminate any -305 SQL abends.

Note: Handle Null values diligently

2. Order By statements

Here’s a way to check the ORDER BY statements. Check all Order By statements. Are they needed? Is there a supporting index for what is being ordered? Sometimes an ORDER BY can be efficient if it matches an index.

Note: Ensure Only index columns present in Order By

3. Duplicates check

Check all Distinct statements. Can there definitely be duplicates? Can such a statement be coded with a Group By or rewritten as a correlated or non-correlated subquery?

In V9, the Distinct works just like the Group By statement in that DB2 will look for sort avoidance by checking for appropriate indexes.

Avoid possible duplicate retrieval areas

Also, Read