Feature Overview
Each time when a correlated subquery is executed, it is necessary to recalculate results of the subquery. This may lead to lower performance. TXSQL currently supports the correlated subquery result cache (subquery cache) feature, which enables caching of subquery results to avoid repetitive calculation and improve the query performance.
Supported Versions
The kernel version should be 3.1.15 or later for TXSQL 8.0.
Applicable Scenarios
It is applicable to scenarios where many correlated subqueries are executed and the query performance needs improvement.
Use Instructions
|
txsql_subquery_cache_enabled | yes | Boolean | OFF | ON/OFF | Controls whether to enable the subquery cache feature. |
txsql_subquery_cache_cost_threshold | yes | Integer | 4000 | 0-DBL_MAX | Execution cost threshold for the subquery cache feature. The subquery cache feature is used only for correlated subqueries with an estimated execution cost higher than this threshold. |
New Status Parameters
New status parameters are used to describe the hit status of the subquery cache, including Txsql_subquery_cache_hit and Txsql_subquery_cache_miss.
You can observe the partial result cache plan using explain format=tree.