I have a query like
SELECT COUNT(*) FROM Foo Where Bar = 1 AND Baz = 2
the table has 12934600 records of which 1000001 match that predicate
Looking at the query statistics I see
(1 row(s) affected) Table 'Foo'. Scan count 1, logical reads 1863, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
(1 row(s) affected)
SQL Server Execution Times: CPU time = 250 ms, elapsed time = 503 ms.
Looking at the query plan 80% of the time is spent in a index seek on the Bar and Baz columns and 20% on aggregating the result.
is there a way to speed this up and if so how? I would also like to understand which hardware components have a big influence here CPU or Disk IO or bus speed.