CODEWITHSUNDEEP
sql-serversql-server-2008-r2query-performance
Buzz
Buzz

Reputation: 1619

SQL Server: Add an index for getting a better performance of COUNT()

If there is a table UserOrder with a clustered primary key (UserID long, OrderID long).

constraint PrimaryKey_UserOrder primary key clustered ( UserID, OrderID)

I find the following count query would become slower and slower after more and more records ( > 1 millions) are inserted into this table.

select COUNT(UserID) from UserOrder where UserID = <some value>

However, if I introduce another non-clustered index for UserID only.

create nonclustered index Index_UserOrder_UserID on UserOrder (UserID)

The query would become much faster because the new index are used instead of primary key.

I am not sure if this new index really helps on the performance of COUNT(). And don't know why.

Any comment?

Note: One user id could have millions of orders.

Upvotes: 0

Views: 44

Answers (1)

Ben Thul
Ben Thul

Reputation: 32697

Depending on the structure of the rest of the table, that index should help with the performance of your query. Let's break it down. Your clustered primary key is on (UserID, OrderID). Because it is the clustered index, that means that every other column in the table is essentially an included column (i.e. exists in the leaf level). Which is to say that at the leaf level, the index is going to be wide. Contrast that with the non-clustered index on UserID. Because you don't have the other columns of the table (except for OrderID) at the leaf level, this index is going to be more compact and thus require fewer reads to satisfy the query. You can prove this by using set statistics io on and forcing your query to use either of the indexes. Using the non-clustered index should perform far fewer reads.

Upvotes: 2

Related Questions