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]