The first and most important step is to enable the SQL query store as described in HTTP JSON API Service — Daml SDK 1.15.0 documentation. The in-memory backend does not perform any caching and I recommend against using it for production usage.
From there on there are a few factors to consider:
- The total number of active contracts that you queried for since you created the database since that’s the set of contracts that we have to store.
- The number of active contracts of the given template (
exampleTemplateId in your example) that are visible to the set of parties in your token.
- The fraction of that for which the
query holds true.
Currently, the database queries are not heavily optimized but we are actively working on that first focusing on Oracle but we will translate the improvements to the PostgreSQL backend as well.
However even without those optimizations, I’d expect that you can get much faster than 5.x seconds if we’re talking < 1 million active contracts in total.