exceed maximum number of prepared statements: the number of prepared statements per connection cannot exceed the max statements

[geert-janklaps]

Description of erroneous behaviour

We have a long running process for initial synchronization (S/4HANA to HANA DB), this process runs easily one hour due to the amount of data. This process runs perfectly fine in systems with limited data, but in productive systems with 40k+ of data, the process runs into an error: "exceed maximum number of prepared statements: the number of prepared statements per connection cannot exceed the max statements"

I found a similar issue in a non-related project: https://github.com/typeorm/typeorm/issues/7344

Looking at the current database driver code, it looks like the prepared statements are not dropped after execution, causing the same issue as described in the referred issue.

I think the issue can easily be resolved by dropping the prepared statements after execution. (I'm using the default hana configuration, with hdb package)

Detailed steps to reproduce

Create a loop with e.g. 100k+ records Insert one by one

=> Our case is doing batch updates, but also combining it with a lot of select statements (reading configuration, determining how data should be calculated and stored)

Details about your project

| ---------------------- | ------------------------------------------------------------------------------------------ | | @cap-js/asyncapi | 1.0.2 | | @cap-js/cds-typer | 0.29.0 | | @cap-js/cds-types | 0.6.5 | | @cap-js/db-service | 1.17.0 | | @cap-js/hana | 1.6.0 | | @cap-js/openapi | 1.1.2 | | @cap-js/sqlite | 1.8.0 | | @sap/cds | 8.7.0 | | @sap/cds-common-conten | 2.1.0 | | @sap/cds-compiler | 5.7.0 | | @sap/cds-dk | 8.7.0 | | @sap/cds-dk (global) | 8.8.2 | | @sap/cds-fiori | 1.3.0 | | @sap/cds-foss | 5.0.1 | | @sap/cds-mtxs | 2.5.1 | | @sap/eslint-plugin-cds | 2.7.0 | | Node.js | v22.11.0 |

[patricebender]

@BobdenOs could you have a look?

[BobdenOs]

@geert-janklaps Could you provide more details on the relation between the SELECT and INSERT your are performing ? As the description of having 40k+ entries and the whole transaction taking possibly over an hour. Doesn't match measured performance potential. Where the average row update rate should be ~100rows/ms which should allow 40k rows to be updated within roughly a single second.

There is a lot of overhead when sending single rows using INSERT or selecting single rows using SELECT. It is possible to use iterators and generators (js) to reduce these kind of overheads.

// Small example on how to do a mass update based upon a list of configs using 2 prepared statements

async function* changes(rows) {
  const configs = cds.ql.SELECT('config_table').where`ID IN (${rows.map(r => r.ID)})`.orderBy`ID ASC`
  rows.sort((a,b) => a.ID > b.ID ? 1 : -1) // Align rows order with config order

  const configIterator = config[Symbol.AsyncIterator]()
  for(const row of rows) {
    const { done, data: config }= await configIterator.next()
    yield {
       ID: row.ID,
       data: config.format == 'json' ? JSON.stringify(rows.data) : rows.data.join(','),
    }
  }
  configIterator.return()
}

await cds.ql.UPSERT(changes()).into('large_table') // will `INSERT` or `UPDATE` the row based upon its existence

It is not possible for @cap-js/hana to drop statements as in certain scenarios objects referencing the statement are returned for external streaming. Therefor the current solution is to drop all the statements once the transaction is done (commit / rollback). Additionally it was experimented to cache prepared statements on the current connection. This mostly worked when using hdb, but when using @sap/hana-client it was not possible at all. While testing there where also some edge cases for hdb therefor the caching proposal was stopped.