Do the Work in SQL, Not the Client

This is the most common structural cause of a slow report, and the one least likely to be found by looking at the query alone — because the query is often not slow.

The pattern

A report is bound to a dataset that returns detail rows. The report definition then does the grouping, filtering, sorting and totalling in the report engine. The report shows twelve summary lines. The dataset returned 480,000 detail rows to produce them.

Nobody notices, because each piece looks reasonable in isolation. The SQL is a simple SELECT with a date filter and runs in four seconds on the server. The report designer's grouping is a couple of clicks. The report takes ninety seconds and everyone blames "the query."

What actually consumes the time:

The server produces 480,000 rows — genuinely 4 seconds, as measured.
Those rows are serialized and sent over TDS — network time proportional to rows × width.
.NET materializes 480,000 objects — allocation, garbage collection, memory pressure.
The report engine groups and aggregates them in memory — the work SQL Server does with a hash aggregate over eight cores, now done single-threaded in managed code.
Layout and rendering.

The database is 5% of the elapsed time and 100% of the blame.

Confirming it is your situation

Three checks, in order of speed.

1. How many rows does the dataset return? Wrap the report's query in a count:

SELECT COUNT_BIG(*) AS rows_returned_to_report
FROM (
    -- paste the report's dataset query here, unchanged
    SELECT TOP (1000) name FROM sys.objects        -- placeholder
) AS report_dataset;

If that number is in the hundreds of thousands and the report displays a summary, you have found it.

2. What does the server think it is returning?

SELECT TOP (25)
       qs.execution_count,
       qs.total_rows / NULLIF(qs.execution_count,0)                AS avg_rows_returned,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000 AS avg_ms,
       qs.total_worker_time  / NULLIF(qs.execution_count,0) / 1000 AS avg_cpu_ms,
       SUBSTRING(t.text, 1, 300) AS query_snippet
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qs.total_rows / NULLIF(qs.execution_count,0) > 50000
ORDER BY avg_rows_returned DESC;

3. Is the client the bottleneck? Run the report and watch for ASYNC_NETWORK_IO (Module 1 Lesson 4):

SELECT  r.session_id,
        r.wait_type,
        r.wait_time / 1000.0 AS wait_seconds,
        r.total_elapsed_time / 1000.0 AS elapsed_seconds,
        r.cpu_time / 1000.0  AS cpu_seconds,
        s.program_name,
        s.host_name
FROM sys.dm_exec_requests  AS r
JOIN sys.dm_exec_sessions  AS s ON s.session_id = r.session_id
WHERE r.session_id > 50
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

ASYNC_NETWORK_IO dominating means the server has rows ready and the client is not keeping up. That is direct evidence the database is not your problem. program_name usually identifies the report engine's connection, which makes it easy to spot.

The fix

Push the aggregation into the query. The report binds to a result set that is already the shape it displays.

-- Before: detail rows, aggregated in the report engine
SELECT  o.OrderId, o.OrderDate, o.CustomerId, o.RegionId,
        o.ProductId, o.Quantity, o.UnitPrice, o.Total
FROM    dbo.Orders AS o                       -- placeholder table
WHERE   o.OrderDate >= @From AND o.OrderDate < @To;

-- After: the twelve rows the report actually displays
SELECT  o.RegionId,
        DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS MonthStart,
        COUNT_BIG(*)        AS OrderCount,
        SUM(o.Total)        AS TotalRevenue,
        AVG(o.Total)        AS AvgOrderValue,
        COUNT(DISTINCT o.CustomerId) AS DistinctCustomers
FROM    dbo.Orders AS o
WHERE   o.OrderDate >= @From AND o.OrderDate < @To
GROUP BY o.RegionId, DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1)
ORDER BY o.RegionId, MonthStart;

Note the WHERE clause still filters on the bare OrderDate column — SARGable, per Module 4. The DATEFROMPARTS expression appears in the SELECT and GROUP BY, which is fine; only functions in the WHERE clause break the seek.

Five hundred thousand rows over the wire becomes twelve. The aggregation happens in a parallel hash aggregate close to the data. The report engine renders twelve rows.

Drill-down without pulling everything

The usual objection: "but users can expand a row to see the detail." Correct, and the answer is a second query, executed only when they expand.

The summary query returns twelve rows. The drill-down query takes the group key as a parameter and returns detail for that group alone — a few hundred rows, on demand, for the one group the user opened. Most users never expand anything, so most of the time you never run it.

Fetching all possible detail up front so that expansion is instant is trading a certain ninety-second wait for every user against a possible two-second wait for the few who drill in.

Filtering, sorting and totals belong in SQL too

The same argument applies to each:

Filtering — a report-level filter expression runs after every row has been fetched. Push it into the WHERE clause and the server never reads those rows.
Sorting — an index in the right order lets SQL Server return rows sorted with no Sort operator at all (Module 3 Lesson 2). Sorting 400,000 rows in the report engine is single-threaded and allocates heavily.
Totals and subtotals — GROUP BY ... WITH ROLLUP or GROUPING SETS compute subtotals and grand totals in one pass:

SELECT  COALESCE(CAST(o.RegionId AS varchar(10)), 'ALL REGIONS') AS Region,
        SUM(o.Total)  AS TotalRevenue,
        COUNT_BIG(*)  AS OrderCount,
        GROUPING(o.RegionId) AS IsGrandTotal
FROM    dbo.Orders AS o
WHERE   o.OrderDate >= @From AND o.OrderDate < @To
GROUP BY ROLLUP (o.RegionId)
ORDER BY GROUPING(o.RegionId), o.RegionId;

Percentages and running totals — window functions, one pass over the data:

SELECT  RegionId,
        MonthStart,
        Revenue,
        SUM(Revenue) OVER (PARTITION BY RegionId ORDER BY MonthStart
                           ROWS UNBOUNDED PRECEDING)          AS RunningTotal,
        Revenue * 100.0 / SUM(Revenue) OVER (PARTITION BY RegionId) AS PctOfRegion
FROM (
    SELECT o.RegionId,
           DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1) AS MonthStart,
           SUM(o.Total) AS Revenue
    FROM   dbo.Orders AS o
    WHERE  o.OrderDate >= @From AND o.OrderDate < @To
    GROUP BY o.RegionId, DATEFROMPARTS(YEAR(o.OrderDate), MONTH(o.OrderDate), 1)
) AS m
ORDER BY RegionId, MonthStart;

Window functions are the tool that removes most remaining reasons to compute things client-side. If a report is doing running totals, rankings, period-over-period comparisons or percent-of-total in the engine, all of those have a single-pass SQL equivalent.

The conversation this requires

Trimming a report's dataset means finding out what the report actually displays and what users actually use. That is a conversation with whoever owns the report, and it is usually the real work — the SQL change afterward takes twenty minutes.

It is also the highest-value thing you can do. There is no index that makes shipping 480,000 rows to a client as fast as shipping twelve.

In practice

The first thing to check on a slow report is how many rows the dataset actually returns versus how many the report displays. If it is pulling half a million detail rows to render twelve summary lines, the query is not the problem — the transfer and the client-side grouping are. Move the aggregation into SQL with GROUP BY, ROLLUP and window functions, and make drill-down a separate parameterized query that only runs when someone expands a row. ASYNC_NETWORK_IO in the wait stats confirms the client is the bottleneck before you change anything.