Entity Cache loads one at a time / issues

I’m using SQL Server for our database and when I have an entity cached in CUBA it will run the query, but then it will redundantly retrieve the records for the cache one at a time. When initially starting our application it will hit the database more than 6000 times to cache each record it has already loaded. Is this expected behavior? The business layer already has the record, can’t it just cache that?

This becomes problematic with cached queries. Every time a cached query runs the system will then again get redundantly retrieve each record for the cache from the database one at a time. It will do this even if the query is already cached. This makes caching queries useless and slower than ever.

This is expected behavior if you run a cached query, but the entities loaded by the query are not cached in entity cache. It is explained here in the docs.

If it’s not the case, please provide more information: the query text, what entities are cached in entity cache.

I’ve done some testing on my own with SQL Profiler running so I can see what is hitting the database or not. I originally though it was an issue with having Entity caching on at the same time as Query caching but it’s not. Here is simple example problem:

I have a collection datasource running the following query “select e from njuns$State e order by e.name”.

With Entity caching on or off it’s a single hit to the database. But when I enable query caching on the data source every time this data source resolves it will query the database one record at a time for each record in njuns$State, in this case it’s 50 hits to the database. It will do this if entity caching is on or off. I don’t know if it’s an issue when using SQL Server or not but it makes query caching worse than useless. The documentation says it saves a hit to the database which definitely isn’t the case here.

Hi Josh,

I’ve done some testing. The problem is reproduced only for entity cache on MSSQL with UUID entities.
Entity cache stores UUID entity key as string in the upper case, because MSSQL returns UUID as string in the upper case. When you find entity by PK, system converts UUID PK to string in the lower case and can’t find entity in the cache. Because cache stores ids in the upper case.

Issue: Entity cache doesn't work for UUID entities on the MSSQL · Issue #1308 · cuba-platform/cuba · GitHub

Fantastic. This appears to be the problem with another issue I was having: https://www.cuba-platform.com/discuss/t/entity-cache-and-select-by-id/5896.

Thank you!

Hi Josh,

We fixed the problem in the 6.9.7.

Thank you.