CODEWITHSUNDEEP
sqlsql-serverperformance
jtiger
jtiger

Reputation: 473

SQL Group By with Count is slow

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:

enter image description here

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.

Upvotes: 4

Views: 2284

Answers (3)

jtiger
jtiger

Reputation: 473

I ended up running this as a nightly aggregate job and storing the result in an aggregate table. It doesn't provide up to date results (which, after deliberation, we can live with) nor is the nightly query any faster, but reading from the aggregate table is a lot faster.

Upvotes: 0

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61529

Try running this, I would also put index as @dean suggested

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

Upvotes: 0

dean
dean

Reputation: 10098

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

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

Upvotes: 1

Related Questions