Improve query performance when selecting almost all rows with many "group by" columns

Question

I have a table with 20 columns and about 600,000 records. The maximum row size is only around 100 bytes. The table is repopulated every few days, but the number of records remain about the same.

For now there is only a single clustered index: an int identity column for the primary key.

I have several queries and views that rely on this table, which usually take 5-10 seconds to execute. When I simply select all records (select * from myTable), it takes about 4 seconds to retrieve all the results.

I haven't been able to find relevant benchmarks for selecting 500,000 records in SQL Server. Is this time typical?

Here is a typical query I perform on the table:

select  CO.Company
    ,CO.Location
    ,CO.Account
    ,CO.SalesRoute
    ,CO.Employee
    ,CO.ProductType
    ,CO.Item
    ,CO.LoadJDate
    ,CO.CommissionRate
    ,SUM(CO.[Extended Sales Price]) AS Sales_Dollars
    ,SUM(CO.[Delivered Qty]) AS Quantity
from    dbo.Commissions_Output CO
where   CO.[Extended Sales Price] <> 0
group by    CO.Company
        ,CO.Location
        ,CO.Account
        ,CO.SalesRoute
        ,CO.Employee
        ,CO.ProductType
        ,CO.Item
        ,CO.LoadJDate
        ,CO.CommissionRate

When I have at least one non-clustered index on the table, I get the following result:

Scan count 18, logical reads 18372; CPU time = 24818 ms, elapsed time = 8614 ms.

I've tried various indices and combinations (index on the filter column, include the group-by columns; index on all filter/group-by columns and include the aggregate columns; etc.). All of them give the same performance and almost always use the same execution plan.

When I remove all but the clustered index (PK), the performance is often improved by up to 3-4 seconds. The logical reads are reduced while the scan count is halved.

Some notes about the data: the results of the select and where clause before grouping are about 500,000 rows (nearly the entire table). Only about 10,000 rows are combined via grouping, which still leaves about 500,000 total records after the group-by.

The execution plan without a non-clustered index shows the most costly operations are a hash match (49%) and a clustered index scan (35%) for the where clause. MSSMS recommends I create a non-clustered index for [Extended Sales Price]. The execution plan with at least one non-clustered index shows the most costly operation is sorting (on the group-by columns).

Given that this query returns almost all records and the group-by barely reduces the number of rows, is this as fast as the query can get? It seems so slow, and I read articles and SO questions about people returning hundreds of thousands of rows in under 1000 ms. Am I missing something, or is this a fairly typical speed? Normalizing this table is currently not an option, and I'm not sure how much that would help.

One last note: I have several views and other queries that involve joining to this table (there is some normalization). At first I thought that those views and queries were slow because of bad joins and such, but it looks like the real culprit is this table and initial queries on it. Most queries and views work with almost all the data in the table. When I'm selecting a single column or a small fraction of rows, then the execution time is fine, but this is rare.

Update: Here are all the execution times, plans and IO statistics. I didn't run each query hundreds of times, but the execution times didn't seem to vary by more than 1000 ms 'hot' vs 'cold'.

No non-clustered index, No MAXDOP setting:

Table 'Commissions_Output'. Scan count 9, logical reads 11263, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

CPU time = 6690 ms, elapsed time = 4605 ms. (max CPU time = 7516 ms, min elapsed time = 3754 ms.)

With non-clustered index, No MAXDOP setting:

Table 'Commissions_Output'. Scan count 16, logical reads 6227

CPU time = 6591 ms, elapsed time = 3717 ms.

No non-clustered index, MAXDOP 1:

Table 'Commissions_Output'. Scan count 1, logical reads 10278

CPU time = 2656 ms, elapsed time = 4991 ms.

With Non-clustered index, MAXDOP 1:

Table 'Commissions_Output'. Scan count 1, logical reads 10278

CPU time = 2656 ms, elapsed time = 4991 ms.

Non-clustered index used:

create nonclustered index IX_NC_Comm_Output on dbo.Commissions_Output([Extended Sales Price])
include (company, location, account, salesroute, employee, producttype, item, loadjdate, commissionrate, [delivered qty])

Answer 1

The non-clustered index you have tested is not the best for this query. It can be used for the WHERE clause and for doing an index scan instead of a full table scan but it cannot be used for the GROUP BY.

The best possible index would have to be a partial index (to filter the unwanted rows from the WHERE clause), then have all the columns used in the GROUP BY and then INCLUDE all the other columns used in the SELECT:

CREATE INDEX special_ix 
  ON dbo.Commissions_Output
    ( company, location, account, 
      salesroute, employee, producttype, 
      item, loadjdate, commissionrate ) 
INCLUDE 
  ( [Extended Sales Price], [Delivered Qty] ) 
WHERE 
  ( [Extended Sales Price] <> 0 ) ;

Answer 2

I'd like to approach the problem from a different angle.

I agree with @ypercube that you can always put up an index to ease on the queries. That said:

• you mentioned that the table holds relatively small amount of data
• table is rebuilt only once every few days
• you shown aggregation over the text columns is the most expensive part of your typical query you will experience even after creating a covering index

Why not go further and create the aggregations beforehand so the queries don't need to the work many times over? Seems like an ideal case for an indexed view, where you would materialize the aggregating query output early on, or a traditional, dedicated table you'd fill when loading data into Commissions_Output. Either way, you're sacrificing only little disk space for much improved performance.

Indexed views do have a bunch of limitations regarding the environment you want to use them in but have a great benefit of being used automatically instead of the original table in some circumstances.