sasinterviewsquestion

Just another WordPress.com site

DB2 SQL Tuning Tips

For Free Programming Workshops http://www.sastrainingdelhi.in
mob: 9990835485

Tip 1
Predicate Evaluation Order: SQL Statements with multiple predicates should be coded in most restrictive to least restrictive order.
Select *
From EMP
Where Sex = ‘F’ ———— 60 % of the rows qualifies
AND Job = ‘Fieldrep’ ———-5 % of the rows qualifies

Should be coded as:

Select *
From EMP
Where Job = ‘Fieldrep’ ———-5 % of the rows qualifies
AND Sex = ‘F’ ———— 60 % of the rows qualifies

Tip 2
Add A.PRSN_INTN_ID = A.PRSN_INTN_ID to change the order in which tables are processed:
You should not add A.PRSN_INTN_ID = A.PRSN_INTN_ID to every SQL Query. Add it only when tuning a query that is not performing well. It may get the optimizer to choose a different access path, which could possibly make it perform better. Use it only when you want DB2 to choose the table that will have least number of rows returned after its predicates are applied.

Tip 3
Use where instead of Having whenever possible.
For example:
Select dept, sum(salary)
From empl_cat
Group by dept
Having dept = ‘A00’

Should be coded as:

Select dept, sum(salary)
From empl_cat
Where dept = ‘A00’
Group by dept

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: