hansva opened a new issue, #9606:
URL: https://github.com/apache/hop/issues/9606

   ### What needs to happen?
   
   Several core paths redo work for every row or every field that only needs 
doing once per row layout. Each one is small, but together they sit under every 
database transform, and some under every pipeline.
   
   **RowMeta: adding n fields costs O(n²)**
   - `addValueMeta` publishes a new lock-free snapshot by copying the whole 
array on every add.
   - The duplicate-name check rescans the field list on every add, because a 
name cache miss is never trusted.
   - Building wide rows, and every transform that extends its output row meta, 
pays that quadratic cost.
   
   **ValueMetaFactory: a plugin registry scan for every value meta it creates**
   - `createValueMeta(type)` asks the plugin registry for the value type's 
plugin and loads its class every time.
   - This runs for every column of every result set that is described, and for 
every other value meta created by type.
   
   **Database: re-resolving the same things per row**
   - `setValue` looks up the dialect's value binding 
(`DatabaseTypeMapper.getBinding`) for every value of every row written.
   - Reading a result row goes through `getValueFromResultSet` per value, with 
the same lookup.
   - `getLookup(PreparedStatement)` resets `rowMeta` on every call, so each 
lookup derives the result row metadata again from `ResultSetMetaData`. That 
creates new value metas, and therefore triggers registry scans, for every 
lookup.
   - In `insertRow`, `isBatchUpdate` stays `true` after a successful 
`executeBatch()`. A failure in the `commit()` that follows is then reported as 
a batch failure.
   
   **Table input with "Execute for each input row"**
   - For every input row, the transform prepares the statement, executes it, 
closes it, and rebuilds the output row layout. The SQL text never changes 
between rows.
   
   **PostgreSQL bulk loader**
   - Every value of every COPY row is encoded and written to the copy stream on 
its own.
   - Dates are formatted through `SimpleDateFormat`.
   
   ### Steps to reproduce
   
   1. Profile a pipeline with Table input (Execute for each input row) → 
Database lookup → Table output against any database. The time spent outside the 
JDBC driver goes mostly to `ValueMetaFactory.createValueMeta`, 
`RowMeta.addValueMeta` / `indexOfValue`, and `DatabaseTypeMapper.getBinding`.
   2. Build a `RowMeta` with a few thousand fields and time the adds: the time 
grows quadratically.
   3. Load a few million rows with the PostgreSQL bulk loader and profile it: 
the time goes to per-value writes and `SimpleDateFormat`.
   
   ### Expected behavior
   
   - Work that depends only on the row layout or the plugin registry is done 
once and reused until the layout or registry changes.
   - Results and generated SQL stay exactly the same.
   
   ### Proposed fix
   
   **RowMeta**
   - The snapshot array is over-allocated. An append fills the next slot and 
publishes a snapshot with one more entry, while removes and reorders still 
copy. Adding n fields becomes O(n).
   - The name cache tracks whether it is complete, so a miss can be trusted 
without a rescan.
   - Fields renamed in place after being added are caught by a new global 
counter, `ValueMetaBase.getNameChanges()`, which is bumped by every `setName` 
that changes a non-empty name. When the counter has moved, the cache rebuilds 
once.
   
   **ValueMetaFactory**
   - Keeps a snapshot of the registry's value-type plugins and the no-argument 
constructor of each type.
   - The snapshot is rebuilt when a new `PluginRegistry.getChangeCount()` 
changes. The counter changes on every register, remove, replace and reset.
   - Plugins that load their own classes, and test doubles of the registry, are 
still asked every time.
   
   **Database**
   - The value bindings of a row layout on a dialect are resolved once into a 
small "binding plan" cache, for writing (`setValues`) and for reading 
(`getRow`).
   - Reading through resolved bindings is only used when the dialect does not 
override `getValueFromResultSet`.
   - `getLookup` keeps the result row metadata per prepared statement.
   - New `openQueryKeepingStatement()`: re-executes the kept prepared statement 
when the SQL and parameter layout are unchanged, closing only the previous 
result set.
   - `insertRow` resets `isBatchUpdate` once the batch has run.
   
   **Table input**
   - With "Execute for each input row", the transform keeps the prepared 
statement and the output row layout, and only binds the next row's parameters.
   
   **PostgreSQL bulk loader**
   - A reusable `PGCopyRowBuffer` encodes each row and hands it to the copy 
stream in one write. ASCII text is copied directly when the client encoding 
allows.
   - Dates use `FastValueFormatter` from #9601 where the output is provably 
identical.
   - The bytes written are unchanged.
   
   ### Tests
   
   - Table input: each input row is queried with its own parameters; the 
statement and row layout are kept across rows and re-prepared when they change.
   - RowMeta: adds, inserts, removes and replaces; duplicate names; renaming a 
field after it was added; concurrent readers during appends.
   - ValueMetaFactory: plugins registered, replaced and removed at runtime, and 
a registry reset.
   - The existing core, dialect and transform test suites pass unchanged.
   
   ### Issue Priority
   
   Priority: 2
   
   ### Issue Component
   
   Component: Database


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: [email protected]

For queries about this service, please contact Infrastructure at:
[email protected]

Reply via email to