SQL Optimization

Many SQL statements can be optimized. When designing statements, consider these recommendations:

• Limit number of table joins. Only include a table in a join when it is necessary. Think about the order in which the tables are joined. Ensure the database handles the table you expect the smallest result set from first, then there are fewer rows to compare with when succeeding tables are joined.
• Keep the statements simple. The more complicated a statement has become, the more complicated it probably is for the database to execute. If possible make sub-queries. For example you can divide complicated statements into several smaller statements that are called successively.
• Database functions. Some databases support many functions and are also good at executing these functions. But others just support them. The only way to check to see whether a performance is considerably degraded is to use a function that tests it and compares execution times.
• Setting up the ODBC Data Source against DB/2 on AS/400 Infor M3 BE.