SQL Tuning Tips

Inadequate performance can have a significant cost impact on your business. The PeopleSoft application is an extremely powerful and highly configurable product that utilizes many different hardware and database platforms. A poor performing system and application can result in customer dissatisfaction, reduced productivity, and high costs. It is absolutely critical that the system's performance is operating at its peak levels.

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

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