Fixing an ORM's inefficient aggregation query

WHAT THIS TESTS Whether you recognize that ORMs excel at transactional CRUD but often generate poor SQL for complex aggregation, and can choose between escaping the ORM and tuning within it.

A GOOD ANSWER COVERS First, capture the actual generated SQL and its execution plan so you optimize evidence, not guesses. Strategy one is to bypass the ORM for this report: write raw SQL, or encapsulate it in a database view or materialized view, or a stored procedure, and map the result to a lightweight read model or DTO rather than full entities. Pros: full control over joins, window functions, and aggregation, and the best performance. Cons: the SQL is now coupled to the physical schema, less portable across databases, and lives partly outside the application code, slightly hurting maintainability. Strategy two keeps the ORM but fixes the query: select only needed columns via projections instead of hydrating whole entities, replace N+1 lazy loads with explicit join fetches or batched loads, push aggregation into the database rather than the application, and add or correct indexes on the grouped and filtered columns. Pros: stays in one codebase, portable, maintainable. Cons: may not match hand-tuned SQL for very complex reports.

COMMON WRONG ANSWERS Forcing the ORM to express analytics it handles badly, optimizing without reading the plan, or jumping straight to raw SQL when an index or projection would suffice.

LIKELY FOLLOW-UPS When a materialized view beats live queries; read-model versus entity mapping; how caching the report fits in.

ONE CONCRETE EXAMPLE A monthly revenue-by-region report makes the ORM hydrate thousands of order entities and aggregate in application memory, taking many seconds. Replacing it with a single SQL query using GROUP BY and a covering index, mapped to a small DTO, returns in milliseconds, while routine order CRUD stays on the ORM where it is convenient and safe.