Reputation: 473
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.
Upvotes: 4
Views: 2284
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
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
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