CODEWITHSUNDEEP
sqlsql-servert-sql
Stig
Stig

Reputation: 1299

TSQL Count(Id) performance with where clause

Currently I have a table with approximately 12 Million Rows and I need the total the number of rows which meet a certain criteria as per the where clause. The query is run once a day outside of peak hours.

e.g.

Select Count(Id) from TableName where ColumnName = X

This is currently taking in the region about a minute to return the correct number of rows. I don't know how I can improve the performance when a where clause is required in the above case. On the database in question the Id,ColumnName is in a clustered Index.

Normally I would use the following Sql if I only wanted the number of rows in a table

SELECT OBJECT_NAME(object_id), SUM(row_count) AS rows
FROM sys.dm_db_partition_stats
WHERE object_id = OBJECT_ID(@TableName)
AND index_id < 2
GROUP BY OBJECT_NAME(object_id);

However I don't see any way of stipulating a where clause

The obvious solution is to increase the timeout as its run out of peak hours however, i would only prefer to do that as a last resort.

Thank you for your help

Ps SQL Server 2008

Upvotes: 1

Views: 496

Answers (2)

Hemanth Vanal
Hemanth Vanal

Reputation: 89

Yes for indexing you apply as Linoff answered

you can try is as well

Select Count(1) from TableName where ColumnName = X ;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269553

This is your query:

Select Count(Id) from TableName where ColumnName = X  ;

You have described the clustered index as "id, ColumnName". If you want to use an index effectively, it needs to be in the other order. So try defining:

create index tablename_columnname_id on tablename(columnname, id);

If id is never NULL, you can write the query as:

Select Count(*) from TableName where ColumnName = X  ;

This will save the NULL check on id.

Upvotes: 0

Related Questions