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]

Reply via email to