Importance Of SQL Design

Importance Of SQL Design

July 16, 2009 Mohammed Mehraj Hussain

 

If the sql statement  is designed poorly, nothing much can be done by optimizer or indexes

Few are the well known rules

-Enabling indexes to eliminate the need for full table scans

-Avoid Cartesian joins

–Use UNION ALL instead of UNION – if possible

–Use EXIST clause instead of IN – (Wherever appropiate)

–Use order by when you really require it – Its very costly

–When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!

–Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0

- Avoid writing where is not null. nulls can prevent the optimizer from using an index

- Avoid calculations on indexed columns. Write WHERE amount > 26000/3 instead of WHERE approved_amt/3 > 26000