SQL Tuning Process

Procedure

1. Collect all table statistics associated with the SQL statements. In a database, statistics serve as the raw data for the planner to generate execution plans. Failure to collect statistics or having outdated statistics can lead to severe degradation of execution plans, resulting in performance issues. According to the experience data, about 10% performance issues occurred because no statistics are collected. For details, see Updating Statistics.
2. 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. SQL Statement Rewriting Rules introduces several common tuning methods through SQL statement rewriting.
3. 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.
4. For details about locating the specific causes of slow SQL execution and providing solutions based on the EXPLAIN or EXPLAIN PERFORMANCE information, see Advanced SQL Tuning.
5. Specify a join order; join, stream, or scan operations; number of rows in a result; or redistribution skew information to optimize an execution plan, improving query performance. For details, see Hint-based Tuning.
6. (Optional) DataArts Fabric SQL supports operator parallelism to enhance performance when resources are abundant. For details, see SMP Parallel Execution.