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]
