hansva opened a new issue, #9601:
URL: https://github.com/apache/hop/issues/9601
### Apache Hop version?
2.20.0
### Java version?
21
### Operating system
Other
### What happened?
SQL file output is much slower than it needs to be. It redoes for every row
work that depends only on the row layout.
For each row, `SQLFileOutput` calls `Database.getSqlOutput()`, and that call:
- quotes the schema and table name again, and quotes every column name again
(each quote also checks the reserved-word list);
- creates a new `SimpleDateFormat` for every date value when a date format
is set, and new Oracle date formatters for every row;
- formats every Integer, Number and Date value through `DecimalFormat` /
`SimpleDateFormat`, even with the default masks;
- builds the statement as a `String`, adds `";"` and the line break by
concatenation, and copies it again with `toCharArray()` before writing;
- writes through a 5,000-byte buffer, which is only 10 to 50 statements.
The table name, the column list and the formatters never change during a
run, so all of that is repeated for nothing.
### Steps to reproduce
1. Create a pipeline: Generate rows (1,000,000 rows, a few string, integer,
number and date fields) → SQL file output.
2. Run it and note the rows/s.
3. Profile it: most of the time goes to `quoteField`, creating
`SimpleDateFormat` instances, and `DecimalFormat`.
### Expected behavior
The INSERT prefix (`INSERT INTO schema.table(col, ...) VALUES (`) and the
formatters are worked out once per row layout. Each row only renders its
values. The file contents stay byte-for-byte the same.
### Proposed fix
- **`SqlInsertTemplate` (core):** builds the quoted INSERT prefix and the
date formatters once, then appends each row's values to a reused
`StringBuilder`. `Database.getSqlOutputTemplate()` creates one, and
`Database.getSqlOutput()` now delegates to it, so the output has a single
implementation.
- **`FastValueFormatter` (core):** renders Integer, Number, BigNumber and
Date values without `DecimalFormat` / `SimpleDateFormat`, but only in the
configurations where the text is provably the same:
- default numeric masks with ASCII symbols;
- date masks built from plain y / M / d / H / m / s / S fields.
Anything else (other masks, locales with non-ASCII digits or symbols,
values out of range) takes the existing path.
- **SQL file output:** creates the template on the first row, reuses one
statement buffer, writes the `String` directly, and uses a 64 KB output buffer.
### Tests
- Unit tests that record SQL file output's current output for every value
type, null values, date formats, the Oracle date path, "start new line", and
the "create table" / "truncate" statements.
- A golden test that runs `getSqlOutput` over many rows across Generic,
PostgreSQL, MySQL, Oracle and SQL Server, and across several locales (including
German, Swedish and Arabic), and checks the output is byte-for-byte unchanged.
- `FastValueFormatter` tests that compare its output with
`IValueMeta.getString()` over random values and every mask, and check it falls
back where it must.
### 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]