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.