Describe the bug
The Data Query Tool's dictionary endpoints (/dictionary/categories and /dictionary/module/<name>) become very slow on databases with many visit labels. On an instance with ~32k candidates, ~66k flag rows and 172 distinct visit labels, /dictionary/categories takes ~72 seconds to respond, and the tool sits on "Loading…" the whole time before the welcome screen appears.
To Reproduce
- Use a database with a large number of distinct
session.Visit_label values.
- Log in and open the Data Query Tool.
- Observe that the initial load blocks on
GET /dictionary/categories for a long time.
Expected behavior
The dictionary endpoints should return in roughly constant time regardless of the number of visit labels. Fetching the accessible instruments in a single query over all of the user's visits brings /dictionary/categories down from ~72s to ~2s on the same instance, with byte-for-byte identical output.
Describe the bug
The Data Query Tool's dictionary endpoints (
/dictionary/categoriesand/dictionary/module/<name>) become very slow on databases with many visit labels. On an instance with ~32k candidates, ~66kflagrows and 172 distinct visit labels,/dictionary/categoriestakes ~72 seconds to respond, and the tool sits on "Loading…" the whole time before the welcome screen appears.To Reproduce
session.Visit_labelvalues.GET /dictionary/categoriesfor a long time.Expected behavior
The dictionary endpoints should return in roughly constant time regardless of the number of visit labels. Fetching the accessible instruments in a single query over all of the user's visits brings
/dictionary/categoriesdown from ~72s to ~2s on the same instance, with byte-for-byte identical output.