17. Improving Database and Query Performance : Design Issues and Performance : Query Design and Performance
 
Share this page                  
Query Design and Performance
Query design is a complex subject. Following these tips will improve the performance of your queries:
Conversion joins are joins where two columns of different data types are joined in a query, either explicitly or implicitly. These joins are frequently the result of database design problems and must be avoided.
Avoid using function joins.
Functions in the WHERE clause force a full-table scan.
Control uppercase and lowercase, and so on, at input time.
Some complex OR queries can be rewritten as unions.
Evaluate QEPs for critical queries:
Can large table scans be avoided?
Is an additional index needed?
Are Cartesian products with large tables used?
Are function joins used?
Use repeated queries for queries that are used many times.
Do not forget to commit. Consider using SET AUTOCOMMIT ON.