Application practice of high-speed caching technology for INFORMIX database SQL statements

As we know, the Informix database optimizer is based on "cost" optimization, and when a user-issued SQL statement is received, the Informix Optimizer compiles the SQL statement to generate the final execution plan based on the lowest cost principle. For each SQL statement, the compilation is done, in order to improve the performance of the system, especially for systems with a large number of identical SQL statements, Informix starts with the 9.2 version, providing SQL statement caching mechanism SQL Statement cache (SSC), Cache parsed and optimized SQL statements into memory so that multiple users executing the same SQL statement can achieve the following performance improvements:

The response time is shortened because of bypassing the analysis and optimization steps

The amount of memory consumed is reduced because the database server shares query data structures among users

By using the SQL statement caching mechanism, the performance of a large number of identical SQL statement systems can be greatly improved.

The following figure shows how the database server accesses SQL statement caching for multiple users.

Figure 1. Database server operations when using SQL statement caching

When the database server executes the SQL statement for User 1 for the first time, the database server checks whether the exact same SQL statement is in the SQL statement cache. If it is not in the cache, then the database server analyzes the statement, determines the optimal query plan, and executes the statement.

When user 2 executes the exact same SQL statement, the database server looks for the statement in the SQL statement cache without parsing and optimizing the statement.

Similarly, if user 3 and User 4 execute exactly the same SQL statement, the database server does not have to parse and optimize the statement. Instead, it uses the parsing information and the query plan in an in-memory SQL statement cache.