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
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
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
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)
Here are some very simple yet powerful SQL tips to remember
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
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
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)
