CODEWITHSUNDEEP
sql-server
Balok
Balok

Reputation: 51

SQL Server: Performance of count() with where clause

This is a query I have

SELECT COUNT(*) 
FROM tbl 
WHERE intCol = @intval 
  AND varcharCol = @varchr 
  AND (datetimeCol BETWEEN @from AND @to)

The query is taking 20 seconds on a table with 6 million records. It's very bad for me as the data can be 20 times more. Is there a way to make it faster?

I have a combined index in place on (intCol, varcharCol, datetimeCol) and the execution plan shows an index scan (cost 91%) -> stream aggregate (cost 9%) -> Compute Scalar (cost 0%)

Highly appreciate any help

Upvotes: 3

Views: 277

Answers (3)

square_particle
square_particle

Reputation: 524

May I ask what the data types are in your table? Also, what type of machine are you running on?

Something doesn't seem to be adding up. I came up with some test data and ran the query above and the query executes almost instantly, even when running with maxdop 1.

Here's my test data:

create table dbo.tbl (
    intCol int,
    varcharCol varchar(128),
    datetimeCol datetime);

insert tbl (
    intCol,
    varcharCol,
    datetimeCol)
select top (6000000)
    abs(checksum(newid())) % 100,
    abs(checksum(newid())) % 100,
    convert(datetime, abs(checksum(newid())) % 50000)
from sys.all_columns a
cross join sys.all_columns b;

create nonclustered index uc
    on tbl (intCol, varcharCol, datetimeCol);

Below you can choose any combination of numbers as long as they are within the domain of allowable values and you are almost guaranteed matches, because the size of the data set is substantial.

declare
    @intval int = 50,
    @varchr varchar(128) = '55',
    @from datetime = '1900-01-01',
    @to datetime = '1950-01-01';

set nocount on;
set statistics io on;
set statistics time on;

select count(*) -- select *
from tbl
where intCol = @intval
    and varcharCol = @varchr
    and datetimeCol between @from and @to
option (maxdop 1);

set statistics time off;
set statistics io off;

The results show that the execution time is de minimis.

-----------
221

Table 'tbl'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.


 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Also, the query plan that I see is similar to what you mentioned in your original post. This is a typical execution plan and cost for a simple query with an aggregate and a where clause.

Query Cost

Upvotes: 3

Nazir Ullah
Nazir Ullah

Reputation: 610

Simple use this line of code for quick counting 

SELECT     count(1)
FROM            tbl

Upvotes: 0

Cleriston
Cleriston

Reputation: 770

Replace count(@TheIDOfyourTable)

Upvotes: 0

Related Questions