SQL Group By with Count is slow

Question

I have a table with close to 3 million rows that has 5-10 updates/inserts every second. Each row is assigned a category, and I want to group by the category to count the total number of rows for each category.

Select CategoryId
     , COUNT(*) as TotalRows
  FROM Table1
 WHERE SaleTypeId = 2 AND CategoryId > 1
 GROUP BY CategoryId

Table Schema:

CREATE TABLE [dbo].[Table1](
[SaleId]      INT IDENTITY (1, 1) NOT NULL,
[SaleTypeId]  INT                 NOT NULL,
[CategoryId]  INT                 NULL)

Primary Key:

 ADD CONSTRAINT [PK_Table1] 
 PRIMARY KEY CLUSTERED ([SaleId] ASC) 
 WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, 
       IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

I have a non-clustered index on the table:

CREATE NONCLUSTERED INDEX [Index1] ON [dbo].[Table1]
(
    [SaleTypeId] ASC,
    [CategoryId] ASC    
)

Query Plan:

The query takes 40 to 60 seconds to run, and it looks like a lot of data is being read in the index seek operation. Is there any way to speed up this query? I have read that count gets slower on bigger data sets and that there are quicker ways to get the count of an entire table, but I need to get the count by the category.

Answer 1

Reverse the columns order in the nonclustered index, like this:

CREATE NONCLUSTERED INDEX [Index1] ON [dbo].[Table1]
(
    [CategoryId] ASC,    
    [SaleTypeId] ASC
)