Wednesday, May 14, 2008

SQL Tuning Tips



Here are some very simple yet powerful SQL tips to remember

Avoid using the following:



 Boolean operators >, <, >=, <=, is null, is not null


 Not in, !=


 Like '%pattern', not exists

 Calculations on unindexed columns or (use union instead)


 Having (use a WHERE clause instead)





Do use the following:


 Enable aliases to prefix all columns


 Place indexed columns higher in the WHERE clause


 Use SQL Joins instead of using sub-queries


 Make the table with the least number of rows the driving table by making it first
in the FROM clause





Do use the following:



 Enable aliases to prefix all columns


 Place indexed columns higher in the WHERE clause


 Use SQL Joins instead of using sub-queries


 Make the table with the least number of rows the driving table by making it first in the FROM clause.




Other important points for SQL Tuning

Establish a tuning environment that reflects your production database




 Establish performance expectations before you begin


 Always Design and develop with performance in mind


 Create Indexes to support selective WHERE clauses and join conditions


 Use concatenated indexes where appropriate


 Consider indexing more than you think you should, to avoid table lookups


 Pick the best join method


 Nested loops joins are best for indexed joins of subsets


 Hash joins are usually the best choice for "big" joins


 Pick the best join order


 Pick the best "driving" table


 Eliminate rows as early as possible in the join order


 Use bind variables. Bind variables are key to application scalability


 Use Oracle hints where appropriate


 Compare performance between alternative syntax for your SQL statement


 Consider utilizing PL/SQL to overcome difficult SQL tuning issues


 Consider using third party tools to make the job of SQL tuning easier

No comments: