hansva opened a new issue, #9647:
URL: https://github.com/apache/hop/issues/9647
### What would you like to happen?
With "Execute for each row", Execute SQL script does several things per row
that only need doing once, and it has no way to group rows into a transaction.
- **A new statement for every row.** `Database.execStatement(sql, params,
data)` prepares a new `PreparedStatement` for every input row and closes it
again, even when the SQL text is the same for every row: with "Bind parameters"
on, or with "Variable substitution" off and no `?` markers to replace.
- **A connection lookup per row.** When values are substituted into the SQL
text, the transform calls `getPipelineMeta().findDatabase(...)` for every row,
only to quote string values.
- **A commit per row.** On a connection that is not in auto-commit mode, the
transform commits after every row. There is no setting to commit every N rows,
so loading many rows this way is limited by commit latency.
- **Row counting is duplicated.** Execute SQL and `Database` each decide on
their own whether a statement's update count is an insert, update or delete
(`MERGE`, `UPSERT`, `REPLACE`, ClickHouse `ALTER TABLE ... UPDATE`).
### Expected behavior
- When the SQL text of a row is the same as the previous row's, the prepared
statement is executed again with the new parameters instead of being prepared
again.
- A user can choose to commit every N rows when executing for each row. Rows
are only passed on once their transaction is committed, so no row is reported
as done while it can still be rolled back.
- The update count of a statement is classified in one place.
### Proposed fix
**Prepared statement reuse**
- For a single statement executed for each row, keep the `PreparedStatement`
while the generated SQL text stays the same.
- Prepare it again when the text changes. Close it on errors and in
`dispose()`.
- Resolve the connection metadata used for quoting once, not per row.
**Commit size** (new option, "Commit size", only enabled with "Execute for
each row")
- Empty or 0 keeps today's behaviour: every row is committed on its own.
- With N, rows run in one transaction that is committed every N rows and at
the end.
- Output rows and their counters are held until their transaction commits.
- When the transform fails or is stopped, the open transaction is rolled
back. The number of rows that were not committed is logged ("N rows that were
not committed are rolled back and not passed on").
- With an error hop, each row runs in a savepoint
(`Database.runInSavepoint`, as in #9106), so a failing row goes to the error
hop without aborting the transaction on databases like PostgreSQL.
- The value accepts variables, is stored as `commit_size` and can be
injected.
**Row counts**
- New `SqlQueryClassifier.affectedRows(sql, databaseMeta)` returns which
counter (output, updated, deleted, or none) an update count belongs to.
- `Database` and Execute SQL both use it, so the counts stay identical.
### Tests
- Tests that pin today's behaviour first: execute once, execute for each
row, single and multiple statements, bind parameters and text substitution with
and without quoting, the inserted/updated/deleted/read result fields, and
error-hop routing.
- Statement reuse: the same text is prepared once; a changed text is
prepared again; the statement is closed on error and in `dispose()`.
- Commit size: commits every N rows and at the end; rows are held until they
are committed; rollback and the discarded-row message on failure and on stop; a
variable in the commit size; an invalid value.
- With an error hop and a commit size: a failing row goes to the error hop,
and the other rows of its transaction are committed (H2 and PostgreSQL).
- Row classification for every verb and for the ClickHouse dialect.
### Issue Priority
Priority: 2
### Issue Component
Component: Transforms
--
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]