Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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: nonc_nomaxdop

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: nc_nomaxdop

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

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

No non-clustered index, MAXDOP 1: nonc_maxdop

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

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

With Non-clustered index, MAXDOP 1: nc_maxdop

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])
share|improve this question
    
It looks like you've got a 4 core CPU. Your CPU time is 24.8 seconds and your elapsed time is 8.6 seconds, so that's my guess. I would be interested in seeing three things. 1) The physical I/O (SET STATISTICS IO ON) (unless your physical is just 0). 2) The two execution plans (actual, not just estimated). 3) try OPTION( MAXDOP 1 ) and compare how long it takes to run. Just curious how much bang for the buck you're getting out of paralellism. –  efesar Jun 20 at 20:07
    
Also, aggregating 500,000 records can take a while ... To be honest though, your timings are a little bit high. I just summed up a single money column in a table with 24 million records and it took 9 seconds cold and 1 second warm. I included your <> 0 predicate. Then I grouped by the amount (resulting in 272671 records) and it still only took 6 seconds. Maybe it's because you're grouping by so many columns. Curious! –  efesar Jun 20 at 20:11
    
@efesar I'm not sure if it's because I was using a different index or if SQL Server did some auto-magic to choose an optimal execution plan, but I no longer see 'Sorting' in either the actual or estimated execution plans. That said, even if the grouping slows things down, when I select just the relevant columns (no grouping or aggregates), it still takes at least 3 seconds. If it were taking 30 seconds or 30 minutes, there would be a problem. Maybe I have no cause to complain here? –  Zairja Jun 20 at 21:01
    
Have you tried adding an index specifically for this query? It would essentially be duplicating all the table's data but if efficiency is crucial for this query... And I mean an index on (company, location, account, salesroute, employee, producttype, item, loadjdate, commissionrate) INCLUDE ([Extended Sales Price], [Delivered Qty]) –  ypercube Jun 20 at 21:01
    
@ypercube Yes, I have. The performance ended up the same or worse. Maybe I didn't create it correctly? What would that index look like? I've done a variation of the NC index above (except on all columns with no include). –  Zairja Jun 20 at 21:02

2 Answers 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.

share|improve this answer

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 ) ;
share|improve this answer
    
This will probably be my accepted answer unless something amazing comes in the next 24 hours. This index had a negligible, but measurable, impact on performance. I think the real bottleneck may be IO, so I'm looking into that now. If you have any baseline data / benchmarks for basic select queries similar to mine, that'd be great. My Google-fu has yielded nothing. –  Zairja Jun 20 at 21:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.