CODEWITHSUNDEEP
sqlsql-serverperformanceoptimizationcount
SidAhmed
SidAhmed

Reputation: 2352

Get records count from sql server table in the most optimized way

I want to get the records count from an sql server table with more than 100 000 records, I'm using the Count(Id) method, but it take a lot of time (about 7 to 8 secondes), is there any way to make it faster, knowing that I am using a where clause in my Select

SELECT Count(StudentID) WHERE Type = 1 AND Name Like %Sid%

I found that we can use the following method 

SELECT rows FROM sysindexes 
WHERE id = OBJECT_ID('dbo.StudentID') AND indid < 2

But unfortunately I can not add my WHERE clause to this. Is there away to do it ?

My Index definition for the Type field : (Non unique, non cluster)

Upvotes: 3

Views: 6849

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180887

The most efficient way to get your count with conditions is using COUNT().

If you have a valid index on Type (that means, it's the first column of an index), it's better to count that very field or * so that the database does not need to fetch StudentID to count it. In other words;

SELECT COUNT(Type) FROM Students WHERE Type=1

or

SELECT COUNT(*) FROM Students WHERE Type=1

Note that this will change the meaning of the query slightly, if StudentID can be null, the row would not have been counted before, but will now.

Upvotes: 2

Daniel Renshaw
Daniel Renshaw

Reputation: 34177

Note that sysindexes.rows will not necessarily provide an exact answer and, as you mention, you can't filter to a subset of the records.

If you need to know the exact number of records, you must use COUNT(*) or COUNT(<column_name>) (depending on whether you want NULLS to count or not).

In your case, if you have an index that has the column Type as its first column, the query optimizer should use that index to speed up the count automatically.

Upvotes: 1

Related Questions