REST API testexec detailed=true triggers N+1 queries, ~68s response time (TestRunBeanFactoryImpl)

The Xray DC REST endpoint /rest/raven/1.0/api/testexec/

{testExecKey}/test?detailed=true suffers from an N+1 query pattern when building the detailed response, causing extremely slow response times and timeouts for downstream integrations (e.g., customers' Power BI import).

Pre-conditions

Xray DC instance. A Test Execution with manual Tests that have steps, linked defects, requirements, and stories/tasks. Endpoint called with detailed=true.

How to reproduce

On Xray DC 8.4.1-j10, take a Test Execution with a moderate dataset (e.g. 29 Tests, ~12 steps/Test, 1-2 Test Runs/Test, ~147 linked Requirements, ~5 linked Defects/Test Run, ~60 linked Stories/Tasks, 0 attachments).

Call GET /rest/raven/1.0/api/testexec/{testExecKey}

/test?detailed=true&limit=20&page=1

Enable DEBUG SQL logging and capture the full request duration.

Compare against the same call with detailed=false (fast) and against a minimal Test Execution (~150ms).

Actual result/Attachments

Request takes ~68,308 ms to complete. DEBUG SQL logs show ~7,036 SQL statements executed for the single request, with repeated per-step queries against AO_8B1069_TEST_STEP_MEMENTO, AO_8B1069_TEST_STEP_RESULT, AO_8B1069_TEST_RUN_DEFECT, and AO_8B1069_ATTACHMENT.

Expected result

The detailed=true response should not require thousands of individual per-step DB/service lookups; Test Step Mementos, Step Defects, Attachments, and Step Custom Field values should be batch-loaded (or cached per request when shared across Test Runs) so response time scales reasonably with dataset size instead of exhibiting N+1 behavior.

Workaround

Partial: if detailed step information is not required, use the same endpoint with detailed=false to avoid the slow path. Not viable for some consumers (e.g., customers' Power BI integration) that require step-level detail.