GBase 8a Implementation Guide: Application Development Optimization

SQL Optimization

1.1 Filter Out Unnecessary Data

When querying tables, filter data as much as possible. SQL can reduce data by minimizing projection columns and adding filter conditions, thereby improving the efficiency of subsequent computations.

1.2 Avoid Cartesian Products in Table Joins

Avoid joining tables without proper join conditions, as this will lead to a large result set and negatively impact performance.

1.3 SQL Rewriting

If performance analysis indicates that the GBase 8a optimizer is not generating the optimal plan, many performance issues can be avoided by rewriting the SQL.

1.4 Use UNION ALL Instead of UNION

Whenever possible, use UNION ALL instead of UNION. The UNION operation requires deduplication, which can significantly impact performance. Ensure that identical data is only inserted once and that there is no duplicate data between different tables to enhance performance with UNION ALL.

1.5 Avoid Table Operations in Custom Functions

Since functions are executed on the compute node, only replicated tables should be operated on within functions. It is recommended to avoid table operations within functions as much as possible.

1.6 Minimize the Use of Cursors

Minimize the use of cursors. Cursor operations are akin to retrieving and processing each row's value individually. If cursors can be replaced with a single SQL statement containing multiple related subqueries, performance will be greatly enhanced.

1.7 Prefer VARCHAR Over CHAR

Whenever possible, use VARCHAR instead of CHAR. The spaces in CHAR can affect performance, and joining CHAR and VARCHAR fields can lead to incorrect joins.