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

   ### Apache Hop version?
   
   2.20.0
   
   ### Java version?
   
   21
   
   ### Operating system
   
   Other
   
   ### What happened?
   
   The caches in Database lookup and Dimension lookup do far more work per row 
than they need to, and Database lookup's "Load all data from table" holds the 
table in memory twice while it loads.
   
   **Database lookup**
   
   - **"Load all data from table" holds the table twice.** It reads the whole 
result with `db.getRows()` into a list and only then copies the rows into the 
cache. While it loads, the table is in memory twice, which runs out of memory 
on large lookup tables.
   - **The read-write cache creates a new key row meta for every row** while 
loading all data, so every cached key carries its own copy of the same metadata.
   - **Each cache entry is wrapped in a `TimedRow`** (a `Date` per entry) only 
to support eviction.
   - **Eviction is approximate and slow.** Once the cache size is exceeded, it 
copies all the keys to a list, samples 10% of the dates, sorts them and removes 
everything older than a cut-off. That happens again every time the limit is 
passed.
   - **Every lookup allocates a new key object** to probe the map, and the 
lookup row is rebuilt with per-field type checks for every row.
   - **Misses aren't cached when the row is dropped or sent to the error hop**, 
so every repeat of that key queries the database again.
   - **Trimming runs on every cache hit** rather than once when the value is 
stored.
   
   **Dimension lookup** (with "Pre-load cache")
   
   - **Every lookup binary-searches all cached rows** with the generic row 
comparator, which compares the natural key field by field through 
`IValueMeta.compare()`. With large dimensions that dominates the time per row.
   
   ### Steps to reproduce
   
   1. Database lookup: point it at a table with a few million rows and enable 
"Load all data from table". Watch the heap while it loads. Then run 1M stream 
rows through it and note the rows/s.
   2. Dimension lookup: pre-load a dimension of a few hundred thousand rows 
with several versions per key. Run 1M stream rows through it and note the 
rows/s.
   
   ### Expected behavior
   
   - "Load all" streams the result into the cache.
   - A cache lookup is a single hash lookup that allocates nothing.
   - Eviction removes the oldest entry exactly.
   - Dimension lookup finds a natural key directly and only searches that key's 
versions by date.
   
   The rows each transform outputs stay the same.
   
   ### Proposed fix
   
   **Database lookup**
   
   - "Load all" iterates the `ResultSet` with `openQuery` / `getRow`, and all 
keys share one row meta.
   - Cache values are trimmed once, when they are stored. A cache hit returns 
them as they are.
   - `DefaultCache` keeps values in a `LinkedHashMap` in insertion order and 
evicts the oldest entry when the cache size is exceeded. `TimedRow` and the 
sampling are removed.
   - A lookup probes the map with one reused key object.
   - The lookup row is built from source indexes, and the conversion flags are 
worked out on the first row.
   - A miss for a row that is dropped or sent to the error hop is cached as 
"not found", so the database isn't asked again. A miss that falls back to the 
default values is cached as before.
   
   **Dimension lookup**
   
   - After the pre-load cache is sorted, `DimensionCache.buildIndex()` maps 
each normalised natural key to the range of its versions in the sorted list.
   - A lookup finds that range with one hash lookup and binary-searches only 
that range, comparing dates as pre-computed longs.
   - For key types it cannot normalise to the same equality that 
`IValueMeta.compare()` uses, the index isn't built and the existing binary 
search is used. Any later change to the cache drops the index.
   
   ### Tests
   
   - Database lookup tests on H2 that check the current behaviour first: load 
all, the read-only and read-write caches, cache size and eviction, trimming, 
default values, failing lookups with and without an error hop, and type 
conversion of keys.
   - Dimension lookup tests that compare the index with the binary search, row 
index for row index, over random dimensions. They cover several key types, 
multiple versions, gaps, open-ended ranges, timestamps with nanoseconds, and 
key types that fall back to the binary search.
   
   
   ### 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