Selecting cache takes several seconds

[urs-beeli]

Description

I have imported a GSAK database containing roughly 45'000 caches, among others GC84QFV. Many other caches in the database are near this cache.

When I select a simple traditional cache, the effect is almost instantaneous, it is immediately selected in the grid and the details (including the map) are shown.

However, when I click on GC84QFV, it takes almost 10 seconds until the selection in the grid and the update of the details is done, during that time no interaction with the grid is possible.

Trying to debug this, I have copied this cache into another database with only 20 caches (and all of them much further away from my cache). Clicking on it still takes longer than on the other caches (3 seconds), but it is much faster than in the large database. I have found some other caches in the large database that are non-instantaneous, but those are comparably fast (2-3 seconds) compared to GC84QFV (10 seconds).

I have tried analysing what makes this cache so special, but have been unable to figure it out.

• the cache has 7 waypoints, other caches in my db have 10+ waypoints are significantly faster
• the cache in the large db has 339 logs, but other caches in my db have over 900 logs and are shown instantly
• the cache in the small db only had 46 logs (taking 3 seconds to select), after I re-imported it from a different GSAK database (the one with 339 logs) it now also takes 10+ seconds for the cache to be selected even in the small db. So it seems that the number of logs is a factor, but apparently not the only one

Is there any way I can support the debugging of this issue by providing additional information? Which information might be needed?

Steps to Reproduce

1. in GSAK: create a new database
2. add 3-4 random caches to it
3. add GC84QFV to it
4. download logs until there are 339 logs in the database
5. start openSAK
6. create a new database
7. import the newly created GSAK database
8. select one of the 3-4 random caches -> instant selection and update of details
9. select GC84QFV -> delay of 10+ seconds before the selection is updated in the grid and the details are shown

Expected Behavior

The time to select a cache should be < 1 second and independent of the information contained in the cache

Environment

python --version = 3.14.6
openSAK Version=1.16.1 (English)
os=Windows 11 Pro 25H2 64-bit German

[AgreeDK]

@urs-beeli @Fabio-A-Sa

Thanks for the detailed repro steps — this is very helpful.

Looking at the numbers, I suspect this isn't really about logs, waypoints, or trackables individually, but about how they combine. Cache detail loading likely uses SQLAlchemy's joinedload() for several collection relationships (logs, waypoints, attributes, trackables) in a single query so everything is available before the session closes.

The catch with joinedload is that when you eager-load more than one collection relationship at once, each one becomes a separate LEFT JOIN, and the joins multiply against each other (a classic SQLAlchemy "collection cartesian product"). So a cache with, say, 339 logs × 7 trackables × 7 waypoints × 8 attributes doesn't return ~361 rows — it can return on the order of 339×7×7×8 ≈ 130,000+ raw SQL rows, which then have to be de-duplicated back into objects in Python. That would explain:

A cache with 900 logs but no trackables loading instantly (only one collection joined → no multiplication)
A cache with 10+ waypoints but few logs/trackables still being fast (low multiplier)
The small test database becoming slow only after re-importing GC84QFV from the larger GSAK export — the re-imported version likely pulled in trackables that weren't there before, turning on the multiplication effect
The 7 trackables vs. 0 trackables on the other caches in the same database (see screenshot in earlier discussion) lining up as the differentiator, not any single count on its own

If this diagnosis holds, the fix would likely be switching the heavier/rarer collections (e.g. logs, trackables) from joinedload to selectinload, which issues one extra query per collection instead of multiplying them together in a single join — this avoids the cartesian product entirely while still avoiding N+1 lazy-load calls after session close.

Not committing to a root cause yet without profiling, but this seems like the most likely mechanism given the pattern in the data above. Will dig into it further.

[urs-beeli]

That analysis makes sense. Large cartesian products slowing down our Java/JPA/Hibernate code is an issue I've encountered before in my "real" job. Luckily, we've switched to a no-sql document store which solved this issue (but comes with other things to solve ;-)

Just an idea following up your proposal of splitting the large join into multiple selectinloads without knowing how easily this is feasible in the current code base: maybe have one joinedload for the "important" stuff and when this completes update the grid and details but have the remaining selectinloads run asynchronously in parallel and display "loading..." in those tabs of the detail view until the queries have completed and then udpate those tabs once the data is available.

Also, in order to show a number of logs or a number of trackables in a cache does not really require the full left join, often a count() subquery is sufficient for such information.

[lignumaqua]

FWIW, GSAK handles this using separate serial SQLite queries for each table within a single transaction; caches, waypoints, logs, and trackables. They each resolve quickly enough such that there is no significant delay in obtaining the full set. Perhaps this is what selectinload() is? (This comment from someone who has no idea what SQLAlchemy is doing beneath the surface as GSAK is all plain vanilla SQLite so take it with a pinch of salt.)

[AgreeDK]

@lignumaqua @urs-beeli
Thanks for your comment, I will look into this after my vacation august 10th.

[aa1vs]

I successfully replicated this using the l&A mega and geowoodstock from this year. There is something magical about the number or type of logs. The L&A mega has less than 300 and works fine. Geowoodstock had 400 imported and has the problem.

Database attached.

landa.zip