How can I optimize a complex SQL reporting query with multiple joins, aggregations, and filters without changing existing business logic rules?

HimanshuSinha 19,637 Reputation points • Microsoft Employee • Moderator

I am working on a production SQL report that joins eight tables, applies several date and status filters, and computes grouped totals for weekly dashboards. Query runtime increased from under one minute to nearly fifteen minutes after adding two new joins and conditional calculations. Existing indexes cover primary and foreign keys, but the execution plan still shows costly scans and hash matches. I cannot change the output schema or business logic, only improve performance and stability. What practical tuning steps should I take first, including index strategy, query refactoring, statistics maintenance, and execution plan analysis, to reduce latency while keeping results fully accurate in production?

  1. Pilladi Padma Sai Manisha 10,190 Reputation points • Microsoft External Staff • Moderator

    Hi @HimanshuSinha
    Thankyou for Reaching Microsoft Q&A!
    Since you cannot change the business logic or the output schema, I would focus on improving how SQL Server executes the query rather than changing any calculations or results.

    The first thing I would do is capture the actual execution plan and identify the most expensive operators, such as table scans, hash matches, sort operations, or key lookups. I would also compare the estimated and actual row counts to see if there are any cardinality estimation issues.

    Although primary key and foreign key indexes already exist, they are often not enough for reporting workloads. I would review the indexing strategy and create targeted composite indexes that support the JOIN conditions, date and status filters used in the WHERE clause, and the columns involved in GROUP BY operations. If the query frequently selects additional columns, adding INCLUDE columns to create covering indexes can help reduce key lookups.

    I would also verify that statistics are up to date because stale statistics can cause the optimizer to choose inefficient execution plans. Updating statistics and performing regular index maintenance can often provide immediate improvements.

    If the query has become difficult for the optimizer to process after adding new joins and conditional calculations, I would consider refactoring it without changing any business logic. Breaking large queries into smaller steps using temporary tables or CTEs can simplify execution and improve plan stability. Repeated calculations should also be computed only once whenever possible.

    Another area to check is implicit conversions. All columns used in JOIN and WHERE conditions should have matching data types, since implicit conversions can prevent index seeks and force expensive scans.

    If query performance varies between executions, I would investigate parameter sniffing and test options such as OPTION (RECOMPILE) or OPTION (OPTIMIZE FOR UNKNOWN) after validating their impact in a non-production environment.

    Finally, I would measure the effectiveness of each change using SET STATISTICS IO and SET STATISTICS TIME to compare logical reads, CPU usage, and execution time before and after every optimization.

    My approach would be to analyze the execution plan first, update statistics, add targeted indexes, eliminate implicit conversions, refactor complex sections only if necessary, and then investigate parameter sniffing. In most reporting workloads, the biggest performance improvements come from execution plan analysis, accurate statistics, and well-designed indexes while keeping the existing business logic and output completely unchanged.

Sign in to comment

1 answer

  1. Erland Sommarskog 134.7K Reputation points • MVP • Volunteer Moderator

    There are several challenges here. The first is: How do you ensure that any rewrite of the query does not change the result? What I do in a situation like this is that I save the result of the query into a table. After having rewritten the query, I run the query again and save to a new table. Then I can run some queries to compare. The most simple-minded are:

    SELECT * FROM #oldVersion
EXCEPT 
SELECT * FROM #newVersion

SELECT * FROM #newVersion
EXCEPT 
SELECT * FROM #oldVersion

    If both result sets are empty, great! This means that the two different versions produce the same result. If they produce different results, this calls for further investigations. The differences between acceptable, for instance because there some parts that are indeterministic so that anything goes. But it also happened to me - more than once! - that I've introduced differences in the business logic, and the new version has proven to be the correct one. For these investigations FULL JOIN queries can be useful.

    As for the query tuning, we cannot say anything about the query as such, as we don't see anything. But if you share the query text and the query plan (in XML format), we might be able to come up with some ideas.

    What we can say from a general perspective is this:

    1. First investigate: Does the rewritten query with the two extra tables actually produce the correct results? It has happened to me more than once when a query has been slow, there was a problem in the logic. A silly error like ON a.col = a.col, or an incomplete join condition. It's useless trying to tune an incorrect query.
    2. Review the query text itself: Are indexed columns entangled in expressions? Are there implicit conversions? If there are, can these be avoided? These are often blockers for using indexes, and thereby trouble makers for performance.
    3. Even if there are plenty of indexes, indexing can still be improved. Maybe some indexes should have some included columns added, to prevent key lookups and dissuade the optimizer from scanning to avoid those key lookups.
    4. Inspect the plan for misestimates. Below each operator, you see things like 546 of 685. That is, the actual number of rows was 546 and the estimate was 685. Such a difference is perfectly OK. But if you use you see 212098 of 1 or the reverse, this worth investigating why this is happening.
    5. Even if the query does not include any blockers I mentioned in #2 and all possible indexes are present, a 10-join query can easily go wrong, as it is a tough game for the optimizer to get it right. A small estimation error can easily to explode. For this reason, it can often help to break up the query in two or more where you store intermediate results in tempt tables. Since the temp table(s) have statistics, the optimizer gets more accurate data when working with the next steps. Of course, then you need to find a good way to break up the query. But here you are in a better situation than the optimizer: Presumably, you know more about the data, than the optimizer that only knows the index and statistics.
    0 comments No comments
    Sign in to comment
Sign in to answer

Your answer