Query
UNWIND $userIds AS uid
CALL (uid) {
MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?})
WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?})
WHERE done.status IN $completedStatuses })
MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta)
WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId)
RETURN ta, training, null AS study
UNION
MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?})
WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?})
WHERE done.status IN $completedStatuses })
MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false})
WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId))
RETURN ta, training, study }
CALL (ta) {
OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?})
WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?})
WHERE newer.status IN $completedStatuses } END
RETURN trainingPA
ORDER BY trainingPA.createdat DESC
LIMIT ? }
WITH uid, training, ta, study, trainingPA
WHERE trainingPA IS NOT NULL
MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2)
CALL (trainingPA, trainingActivity) {
OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2)
WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?}))
WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2)
WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals
WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals
WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals
WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals
WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals
WHERE x[?] = ? | x[?]]), max: head([x IN tpVals
WHERE x[?] = ? | x[?]]), result: head([x IN tpVals
WHERE x[?] = ? | x[?]])} END) AS tpQuizzes
WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores
RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues }
WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog)
WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS
WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog
ORDER BY training.name, training.tildaid
RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog
WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog
WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
Optimizer suggestions — proposals from claude-opus-5-5, 1d ago; suggests only, a human applies
rewrite Drop the unbounded NEXT_PARTICIPANT_ACTIVITY* check in the CALL (ta) subquery
The issueIn `CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA...) WHERE CASE ... ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END ...}`, every completed ParticipantActivity follows the whole NEXT_PARTICIPANT_ACTIVITY chain to the end with no length limit. For an assignment with n attempts that is O(n²) expansions. The work is mostly redundant, because the same subquery already runs `ORDER BY trainingPA.createdat DESC LIMIT ?`, which picks the newest completed PA anyway.
The fixIf createdat always increases along NEXT_PARTICIPANT_ACTIVITY (a newer attempt is created after the one it follows), the newest completed PA by createdat is the same as the completed PA with no newer completed successor. In that case you can remove the NOT EXISTS clause with the variable-length path and let ORDER BY/LIMIT choose. Only the CALL (ta) block changes. Keep the 1-hop NOT EXISTS for the assigned-state branch, because it picks the chain tail, not a status match.
Proposed rewrite — current query in red −, proposed in green +
- UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses AND NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY*]->(newer:ParticipantActivity{deltimestamp:?}) WHERE newer.status IN $completedStatuses } END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
+ UNWIND $userIds AS uid CALL (uid) { MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{ownerOrgId:$orgId, deltimestamp:?}) WHERE coalesce(ta.studyId, ?) = ? AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta) WHERE (coalesce(training.scopeType, ?) = $orgScopeType) AND (training.orgId <> ? AND training.orgId = $orgId) RETURN ta, training, null AS study UNION MATCH (:User{tildaid:uid})<-[:TRAINING_ASSIGNMENT_TO_USER]-(ta:TrainingAssignment{deltimestamp:?}) WHERE ta.studyId IN $studyIds AND (ta.state = $assignedState OR EXISTS { (ta)-[:PARTICIPANT_ACTIVITY]->(done:ParticipantActivity{deltimestamp:?}) WHERE done.status IN $completedStatuses }) MATCH (training:Training{delpending:false})-[:TRAINING_TO_ASSIGNMENT]->(ta), (training)-[:PLAN_DEFINITION_TO_STUDY]->(study:Study{delpending:false}) WHERE study.tildaid = ta.studyId AND (NOT ((coalesce(training.siteScope, ?) = $studyTeamScope) OR (coalesce(training.scopeType, ?) = $orgScopeType)) OR (training.orgId <> ? AND training.orgId = $callerOrgId)) RETURN ta, training, study } CALL (ta) { OPTIONAL MATCH (ta)-[:PARTICIPANT_ACTIVITY]->(trainingPA:ParticipantActivity{deltimestamp:?}) WHERE CASE WHEN ta.state = $assignedState THEN NOT EXISTS { (trainingPA)-[:NEXT_PARTICIPANT_ACTIVITY]->() } ELSE trainingPA.status IN $completedStatuses END RETURN trainingPA ORDER BY trainingPA.createdat DESC LIMIT ? } WITH uid, training, ta, study, trainingPA WHERE trainingPA IS NOT NULL MATCH (trainingPA)-[:BELONGS_TO_ACTIVITY]->(trainingActivity:ActivityV2) CALL (trainingPA, trainingActivity) { OPTIONAL MATCH (trainingPA)-[:PARTICIPANT_QUESTIONNAIRE]-(tpPqnr:ParticipantQuestionnaire)-[:BELONGS_TO_QUESTIONNAIRE]-(tpQnr:QuestionnaireV2) WHERE exists((tpQnr)-[:GRAPH_TO_VARIABLE {graphid: tpQnr.tildaid, path: [tpQnr.tildaid]}]->(:VariableV2 {name: ?, type: ?})) WITH trainingPA, trainingActivity, tpPqnr IS NOT NULL AS tpIsQuiz, tpQnr.title AS tpTitle, tpPqnr.attempts AS tpAttempts, tpPqnr.gradedQuestionCount AS tpGraded, tpPqnr.correctQuestionCount AS tpCorrect, [(tpPqnr)-[:PARTICIPANT_VARIABLE]-(tpPv:ParticipantVariable)-[:BELONGS_TO_VARIABLE]-(tpVar:VariableV2) WHERE tpVar.name IN [?, ?, ?] | [tpVar.name, tpPv.value]] AS tpVals WITH trainingPA, trainingActivity, count(CASE WHEN any(x IN tpVals WHERE x[?] = ? AND coalesce(x[?], ?) <> ?) THEN ? END) AS tpFailed, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotal, sum(toInteger(coalesce([x IN tpVals WHERE x[?] = ? | x[?]][?], ?))) AS tpTotalMax, collect(CASE WHEN tpIsQuiz THEN {title: tpTitle, attempts: coalesce(tpAttempts, ?), graded: tpGraded, correct: tpCorrect, score: head([x IN tpVals WHERE x[?] = ? | x[?]]), max: head([x IN tpVals WHERE x[?] = ? | x[?]]), result: head([x IN tpVals WHERE x[?] = ? | x[?]])} END) AS tpQuizzes WITH trainingPA, trainingActivity, tpFailed, tpTotal, tpTotalMax, tpQuizzes, [q IN tpQuizzes | q.score] AS tpScores, [q IN tpQuizzes | q.max] AS tpMaxScores RETURN tpFailed = ? AND (trainingPA.status = ? OR coalesce(trainingActivity.passingScore, ?) = ? OR tpTotalMax = ? OR tpTotal * ? >= coalesce(trainingActivity.passingScore, ?) * tpTotalMax) AS trainingPassed, tpScores AS trainingScoreValues, tpMaxScores AS trainingMaxScoreValues } WITH uid, training, ta, study, trainingActivity, trainingPA, trainingPassed, trainingScoreValues, trainingMaxScoreValues, [(trainingPA)-[:ALL_CHANGELOG]->(cl:ChangeLog) WHERE cl.type = ? AND cl.newValue IN [?,?,?,?] | [cl.newValue, cl.createdat, cl.who, CASE WHEN cl.newValue = ? AND cl.blob STARTS WITH ? THEN coalesce(apoc.convert.fromJsonMap(cl.blob).completedAt, cl.createdat) ELSE cl.createdat END, cl.reason]] AS trainingLog ORDER BY training.name, training.tildaid RETURN uid, collect({activityId:trainingActivity.tildaid, activityTitle:trainingActivity.title, version:trainingActivity.version, paId:trainingPA.tildaid, paStatus:trainingPA.status, paUpdatedAt:trainingPA.updatedat, paCreatedAt:trainingPA.createdat, paDueDate:trainingPA.dueDate, trainingId:training.tildaid, trainingName:training.name, trainingStatus:training.status, validUntil:training.validUntil, validUntilUnits:training.validUntilUnits, assignmentState:ta.state, studyId:study.tildaid, studyName:study.name, passed:trainingPassed, scoreValues:trainingScoreValues, maxScoreValues:trainingMaxScoreValues, assignments:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]], completions:[x IN trainingLog WHERE x[?] IN [?,?] | [x[?], x[?]]] }) AS trainingActivities
Expected: This removes the variable-length expansions for re-attempted or recertified trainings. For users with long attempt chains, the subquery could get several times cheaper. For users with only one or two attempts there may be little change. The total gain is small because the query ran only twice in 7 days. · Evidence: First confirm the assumption by running a query that counts NEXT_PARTICIPANT_ACTIVITY pairs (a)-[:NEXT_PARTICIPANT_ACTIVITY]->(b) where b.createdat < a.createdat. The count must be 0. Then compare PROFILE db hits for the CALL (ta) block before and after, using a real $userIds set, and diff the result rows to check they are identical.
index Make sure the User.tildaid anchor uses an index
The issueBoth UNION branches start at `MATCH (:User{tildaid:uid})`, run once per element of $userIds. This is the only selective entry point in the statement. Without a lookup on User.tildaid, each uid needs a NodeByLabelScan over every User. With a large $userIds list, that would explain the 1.9 s outlier. The schema for this property was not provided, so it is unverified.
The fixRun SHOW INDEXES WHERE 'User' IN labelsOrTypes. If nothing covers tildaid, create the range index below. A uniqueness constraint is better if tildaid is a unique key, but it will fail if duplicates already exist. If a constraint-backed index already exists, this statement is a no-op, and the anchor is not the cost.
Add this index
CREATE INDEX user_tildaid IF NOT EXISTS FOR (u:User) ON (u.tildaid)
Expected: If the index was missing: each UNWIND row changes from a label scan to an index seek, likely cutting latency many times over when $userIds is large. If the index already exists: no change. The weekly saving is small either way, since there were only 2 executions. · Evidence: PROFILE showing NodeIndexSeek on User(tildaid) for both UNION branches instead of NodeByLabelScan/Filter. Also note that the reported p95 (1900 ms) is higher than the 7-day total (1090 ms), so check the metrics source before relying on the outlier.
Status: proposed · all suggestions
No rollups for this shape in the window.