3 Top things to consider while reviewing the SQL program

The point is while reviewing SQL coded program first you need to look at these three points to avoid any performance issues later on. Why I am stressing this point is if your SQL shows more performance indirectly you can save server costs.

SQL review tips

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

Order By statements

Just take a look you really need 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: Make sure present only index columns in Order By

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.

Note: Avoid possible duplicate retrieval areas

Also Read

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.

Start Discussion

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s