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

   ### Apache Hop version?
   
   2.20.0
   
   ### Java version?
   
   21
   
   ### Operating system
   
   Other
   
   ### What happened?
   
   Checking whether an identifier is a reserved word is slow, and Hop does this 
for every name it quotes.
   
   `DatabaseMeta.isReservedWord(word)` calls `getReservedWords()` and scans the 
result with `Const.indexOfString`. Most dialects build a new array of several 
hundred words (about 700 for PostgreSQL) on every call to `getReservedWords()`. 
So each check allocates the whole list and compares the word against it one 
entry at a time.
   
   This check runs for every name that goes through `quoteField()`, which is 
every table and column name in generated SQL. That makes any code that builds 
SQL per row or per statement slower than it needs to be: SQL file output, 
Combination lookup, Table exists, and the DDL and DML generated by the database 
transforms.
   
   ### Steps to reproduce
   
   1. Create a PostgreSQL connection.
   2. Call `databaseMeta.quoteField("customer_name")` in a loop, or run a 
pipeline that generates SQL for many rows (for example SQL file output with 
many columns).
   3. Profile it: the time goes to `getReservedWords()` allocating the array 
and to `Const.indexOfString` scanning it.
   
   ### Expected behavior
   
   A reserved-word check should be a constant-time lookup in a set that is 
built once per dialect instance, not a new list scanned on every call.
   
   ### Proposed fix
   
   - Add `ReservedWords`: an immutable, case-insensitive set with O(1) lookups. 
It matches words exactly the way `String.equalsIgnoreCase` does, which is how 
`Const.indexOfString` matches them today. It must not use the locale-sensitive 
`toUpperCase()` (Turkish dotless i; ß becomes SS).
   - Add `IDatabase.isReservedWord(String word)` as a default method that keeps 
the current scan, so third-party dialects behave as before.
   - `BaseDatabaseMeta` overrides it with a lookup it builds on first use and 
keeps. Clones share that lookup. A `clearReservedWordsCache()` method is there 
for a dialect whose list depends on state that changes.
   - `DatabaseMeta.isReservedWord()` hands the check to the dialect.
   - `GenericDatabaseMeta` does not cache, because its dialect can be switched 
at any time. It hands the check to the current dialect.
   
   Generated SQL stays exactly the same. In a micro-benchmark against 
PostgreSQL, each check is about 150x faster.
   
   ### Tests
   
   - Unit tests for matching: case, non-ASCII words, null, clones, and the 
default method.
   - Golden tests for Generic, PostgreSQL, MySQL, Oracle and SQL Server that 
record the quoted SQL from before the change and check it is 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