SQL Optimization

The aim of SQL optimization is to maximize the utilization of resources, including CPU, memory, and disk I/O. All optimization methods are intended for resource utilization. To maximize resource utilization is to run SQL statements as efficiently as possible to achieve the highest performance at a lower cost. For example, when performing a typical point query, you can use a combination of Seq Scan and filter (that is, read each tuple and match the point query condition). You can also use Index Scan, which can implement the query at a lower cost but achieve the same effect.

You can determine a proper database deployment solution and table definition based on hardware resources and service characteristics. This is the basis of meeting performance requirements. The following performance tuning sections assume that you have finished installation based on a proper database solution in the software installation guide and performed database design based on the guide for database design and development.

• Query Execution Process
  
• Introduction to the SQL Execution Plan
  
• Optimization Process
  
• Updating Statistics
  
• Reviewing and Modifying a Table Definition
  
• Typical SQL Optimization Methods
  
• Experience in Rewriting SQL Statements
  
• Configuring Key Parameters for SQL Tuning
  
• Hint-based Tuning
  
• Introduction to Plan Trace
  
• Tuning with SQL PATCH
  
• Optimization Cases