bug: `createBaseQueryBuilder` is a performance dragon that must be slayed ⚔️ 🐉

[danoswaltCL]

So i think i've found a monster...while going spelunking this way and that trying to track down why this experiment is not loading correctly in QA, I found that findOneExperiment called in /experiment/:id was to blame because within it we call createBaseQueryBuilder which does one query with exponentially more expensive joins the more decision points, conditions, and segments are involved.

Then looking around I see that we also use createBaseQueryBuilder in csv export and /batch-assign`.... it's not quite as bad per experiment fetch because it is only for one decision point which eliminates one factor, but still not good and it's for every valid experiment in the context.

Claude helped me break this down into something concrete I can understand: The formula is essentially as shown below, this would be the # of rows the query ends up creating.

• D = number of decision points
• C = number of conditions
• I and E = number of segments in inclusion list and exclusion lists one row deep (aka not every nested segment in this particular call, just the root segments to be resolved, but that's not much of a comfort based on discussions we've had about getSegmentById issues. Let's leave that out of this but assume it's making things slower also).

when cache is missed and hits the db:

• /experiment/:id will be (C² × D² × I × E) times slower than it should be
• data-export and /batch-assign) will be (C² × D × I × E) times slower than it should be (for every experiment fetched)

so for express mode, we would be getting (3^2 * 46^2) = 19044 rows at least, multiplied by roughly number of segments involved also, which is almost entirely thrown away by the logic.

in my testing, I am trying to split up these into paralell calls, it is making a gigantic difference, it becomes as quick as just (C x D) (or roughly I x E), which is (138 rows). Everything goes so much faster, including running tests.

[danoswaltCL]

https://github.com/CarnegieLearningWeb/UpGrade/compare/fix/experiment-by-id-slow-sql?expand=1

pure claude answer to how to make this better for /experiment:id for example of what I think we need to do in general, need similar for the /assign call also.

[danoswaltCL]

and some sql with explanation here, based on an experiment in local with 46 dps and 3 conditions:

biggun.json

-- biggun experiment:
-- 0ea4b511-bc5a-48d3-b43e-9a3b11a14a4d
-- (46 decision points, 3 conditions = 138 condition payloads)

-- The Cartesian product: what the old monolithic query produces internally
SELECT COUNT(*) AS cartesian_rows
FROM experiment e
LEFT JOIN experiment_condition c ON c."experimentId" = e.id
LEFT JOIN decision_point p ON p."experimentId" = e.id
LEFT JOIN condition_payload cp_p ON cp_p."decisionPointId" = p.id
LEFT JOIN experiment_condition pc ON pc.id = cp_p."parentConditionId"
LEFT JOIN condition_payload cp_c ON cp_c."parentConditionId" = c.id
WHERE e.id = '0ea4b511-bc5a-48d3-b43e-9a3b11a14a4d';
-- Expected: ~19,000 rows (138 × 138)

-- vs each new split query (what findOneExperiment now runs)
SELECT COUNT(*) AS condition_payload_rows
FROM experiment e
LEFT JOIN experiment_condition c ON c."experimentId" = e.id
LEFT JOIN condition_payload cp ON cp."parentConditionId" = c.id
WHERE e.id = '0ea4b511-bc5a-48d3-b43e-9a3b11a14a4d';
-- Expected: ~138 rows

SELECT COUNT(*) AS partition_payload_rows
FROM experiment e
LEFT JOIN decision_point p ON p."experimentId" = e.id
LEFT JOIN condition_payload cp ON cp."decisionPointId" = p.id
WHERE e.id = '0ea4b511-bc5a-48d3-b43e-9a3b11a14a4d';
-- Expected: ~138 rows

-- Old: still explodes on the condition side even with the partition filter
SELECT COUNT(*) AS cartesian_rows
FROM experiment e
LEFT JOIN experiment_condition c ON c."experimentId" = e.id
LEFT JOIN decision_point p ON p."experimentId" = e.id
LEFT JOIN condition_payload cp_p ON cp_p."decisionPointId" = p.id
LEFT JOIN experiment_condition pc ON pc.id = cp_p."parentConditionId"
LEFT JOIN condition_payload cp_c ON cp_c."parentConditionId" = c.id
WHERE (e.state = 'enrolling' OR e.state = 'enrollmentComplete')
  AND 'upgrade-internal' ILIKE ANY(ARRAY[e.context])
  AND p.site = 'a'
  AND p.target = 'aa';
-- Expected: ~414 rows (1 matching partition × 3 cp_p × 3 conditions × 46 cp_c)
-- vs findOneExperiment's ~19,000 — bad but less catastrophic

[danoswaltCL]

this was downgraded from monster to a mere troll, because /assign is not actually implicated, got a little excited and cross-eyed misreading the maze of similarly named functions.