Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 5 down vote accepted

Bear in mind that the query plan shows an estimate of cost which is not the same as time. The estimated cost values are a unitless aggregation of CPU, memory, and IO, not how long each operation takes to execute. Also bear in mind that the cost values are estimates even in an "actual" execution plan.

The biggest bottleneck for this and most other SQL Server operations is disk IO. Your example STATISTICS looks to be from a second run since there are no physical reads, though, so you see a higher CPU which is used for the aggregation/sorting.

You may be able to speed this particular query up with a filtered index, if your WHERE predicate is consistent (i.e. always Bar = 1 AND Baz = 2).

Otherwise I'm not aware of a way to speed it up beyond something more drastic like an indexed view.

share|improve this answer
1  
I would expect parallelism to improve the response time of this query. If I/O were a factor compression might be useful too, since fewer pages need to be touched. Indexed views don't deserve the tag 'drastic'; where they make sense, they can improve performance by orders of magnitude. There will not be any sorting for a scalar aggregate. –  Paul White Jan 25 '12 at 2:22
    
@SQLKiwi - Creating an indexed view for this particular query seems "drastic" to me, not indexed views in general. they are a lot more involved than the other options though due to the restrictions involved, and I try to avoid them since they can be a maintenance issue (i.e. index gets updated but isn't present on the actual table so may not get disabled when needed). –  JNK Jan 25 '12 at 2:25
add comment

For SQL Server 2008, a filtered index that matches the where clause should speed this up.

If you want to have parameters, not constants, then an indexed view will can have this pre-calculated with a GROUP BY on Bar and Baz

share|improve this answer
1  
It is unfortunate that building an indexed view (pre-2012) takes and holds a Sch-M lock for the duration of the operation, preventing concurrent writes and reads. Not an issue if the operation can be scheduled for a maintenance period, but worth pointing out, perhaps. –  Paul White Jan 25 '12 at 2:28
add comment

You might also be encountering some degree of blocking.

When I test a COUNT(*) on a table with pages pulled from cache it seems to be much more CPU bound than your results show (more like 90% CPU time than 50%).

You could try with NOLOCK and see if that improves matters. If it does you will need to determine whether the COUNT returned is likely to be sufficiently accurate for your purposes.

share|improve this answer
1  
Unless only very rough counts are required and query failure can be tolerated, RCSI/SI might be a better bet than READ UNCOMMITTED. –  Paul White Jan 25 '12 at 2:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.