Implement caching of PostgreSql statements
Type of change
-
Bug Fix -
Feature
Does this introduce a change in the core logic?
- [Yes]
Does this introduce a change in the cloud provider implementation, if so which cloud?
-
AWS -
Azure -
GCP -
IBM
Updates description?
The main goal of this MR is to make caching of PostgreSql statements, currently each new statement building with new name.
This is not effective and may lead to performance degradation due to a lot of prepared statements.
Issue: #73 (closed)
Concept:
- Make deterministic generation name for statement.
- All prepared statements are caching and belong to the particular connection.
- Statement which is already prepared, cannot be overwritten by another SQL query with the same name.
- Statements with the same name shares internal state (for example, two instances will have the same binded arguments and result).
- Prefer to use prepared statements instead direct execute via 'Connection::execute()' (it make sense to use only during application initialization or for rare operations).
Key changes:
- Introduce caching of prepared PostgreSql statements at
Connection
level, they will be kept until destroying. - Change method 'Connection::prepare()', now it returns 'Statement' object (can be taken from the cache).
- Move method 'Connection::executePrepared()' to 'Statement' class, now just 'Statement::execute()'.
- Introduce new methods: 'Connection::getPrepared()
and
Connection::tryGetPrepared()`. - Add ability to pass SQL parameters to method 'Connection::execute()' previously they were in the
executePrepared()
. - Fix
Cursor
mode (currently it's not used in the Open ETP Server), covered by tests. - Refactor
Statement
class implementation. - Remove not used method
Statement::prepareNext()
and related functionality for build series of statements inside one. - Fix/refactor statement tests (they were previously disabled by
#if 0 ... #endif
statement), removed which are not relevant.
Tested that PostgreSQl automatically recovers connection/statements after restarting service (no needed any special actions).
Performance
Implemented mechanism caching of PostgreSql statements allowed to increase overall performance of Open ETP Server for about 15%.
Edited by Pavel Kisliak