Optimize or re-strategize getAllMonitoredDecisionPointLog query

[danoswaltCL]

Version where bug was found:
5

Describe the bug
Query to find monitored experiment counts performs poorly when there are many marked experiments in the monitored_decision_point table. This was a factor in production incident #1061 where version 5 could not be released to production due to runaway database query times.

the query in question is called here via getAllMonitoredDecisionPointLog and was added for v5 to support within-subjects experiments: https://github.com/CarnegieLearningWeb/UpGrade/blob/8a4fcf1bc14ad4e936a19fe33059db3b40dd3958/backend/packages/Upgrade/src/api/repositories/MonitoredDecisionPointLogRepository.ts

The solution for this was to ensure that this query only was called for within-subjects experiments, and to temporarily disable "within-subjects" as choice. See https://github.com/CarnegieLearningWeb/UpGrade/pull/1062/files.

The query either needs to be optimized, or a new strategy to obtain the necessary information for within-subjects "stack" creation needs to be implemented before we allow within-subjects experiments to be used in production.

To Reproduce
The easiest way to reproduce would be to point to a production database snapshot of the database and create a within-subjects experiment. An assign call with an eligible user should produce a query that looks like:

Expected behavior
No bad query times.

[ppratikcr7]

@bcb37 @danoswaltCL Should we close this, as its moved to Done Column?

[danoswaltCL]

hm, do we have a ticket somewhere to handle when we actually do need this call in within-subjects experiments?

[ppratikcr7]

hm, do we have a ticket somewhere to handle when we actually do need this call in within-subjects experiments?

@danoswaltCL I didn't get it. What exactly needs to be handled?