You can analyze slow SQL statements to optimize them.
- Collect all table statistics associated with the SQL statements. In a database, statistics indicate the source data of a plan generated by a planner. If statistics are unavailable or out of date, the execution plan may seriously deteriorate, leading to low performance. According to past experience, about 10% performance problem occurred because no statistics are collected. For details, see Updating Statistics.
- View the execution plan to find out the cause. If the SQL statements have been running for a long period of time and not ended, run the EXPLAIN command to view the execution plan and then locate the fault. If the SQL statement has been executed, run the EXPLAIN ANALYZE or EXPLAIN PERFORMANCE command to check the execution plan and actual running situation and then accurately locate the fault. For details about the execution plan, see SQL Execution Plan.
- Perform the steps in Reviewing and Modifying a Table Definition.
- For details about EXPLAIN or EXPLAIN PERFORMANCE, the reason why SQL statements are slowly located, and how to solve this problem, see SQL Tuning Overview.
- Generally, some SQL statements can be converted to its equivalent statements in all or certain scenarios by rewriting queries. SQL statements are simpler after they are rewritten. Some execution steps can be simplified to improve the performance. The query rewriting method is universal in all databases. Experience in Rewriting SQL Statements describes several tuning methods by rewriting SQL statements.
- Routinely maintain tables and rebuild indexes.
- (Optional) Improve performance by using operators if resources are sufficient in DWS. For details, see Recommended Suggestions for SMP.