Skip to content

Implement caching of PostgreSql statements

Pavel Kisliak requested to merge epam/cache_db_statements1 into main

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()andConnection::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%.

Before this MR: image

After: image

Edited by Pavel Kisliak

Merge request reports