"Performant" conditional COUNT(*)?

Question

I'm using the following:

SELECT
    COUNT(*)
FROM
    [User]
Where
    Username like '%m%'

And the results are returned in the same time as the following:

SELECT
    *
FROM
    [User]
Where
    Username like '%m%'

What's the fastest way to do a conditional count?

What else have you tried and what were the results? ex. `COUNT(1)` etc. — Nick, Aug 11 '16 at 22:38
The query speed is almost always defined by what is after the "where" clause. Therefore your queries run similar times. To speed up a query you could create an index on the username column. Do you already have that? — QuickPrototype, Aug 11 '16 at 22:40

score 1 · Answer 1 · answered Aug 11 '16 at 22:40

1

You're getting comparable times of execution because of the non-sargeable LIKE comparison with leading % symbol.

This way it has to look at the rows and can't pull out information from statistics or indexes - a table has to be visited.

answered Aug 11 '16 at 22:40

Kamil Gosciminski

An index can improve performance here. Since only a count is being returned, there is no need to read the row data other than the column referenced in the `WHERE` clause. Instead of a table scan an index scan can provide somewhat better performance. It depends on the width of the indexed column versus the width of the rows. – HABO Aug 12 '16 at 02:28

score -2 · Answer 2 · answered Aug 11 '16 at 22:38

-2

When you use * in COUNT you fetch all data which is not necessary in your query. Just use any constant to count returned rows.

SELECT
    COUNT(1)
FROM
    [User]
Where
    Username like '%m%'

answered Aug 11 '16 at 22:38

Hamlet Hakobyan

Thanks! I tried this and it gets me a ~25% improvement over 140k records. Not as substantial as I was hoping. – RobVious Aug 11 '16 at 22:40
That is just not true. Read (1) https://social.msdn.microsoft.com/Forums/sqlserver/en-US/31795984-ea62-4b2c-8c78-6e986f2bcea0/count-1-vs-count-?forum=transactsql (2) and https://msbiskills.com/2015/09/16/sql-server-count1-vs-count-vs-count100-which-one-is-fastest/ – Kamil Gosciminski Aug 11 '16 at 22:41
The botleneck in your query is `like %m%` because the predicate is not SARGable. – Hamlet Hakobyan Aug 11 '16 at 22:41
@KamilG., the articles you've provided are very naive, at least second, because the desision was made on estimated execution paln. There not taken in account logical and phisical reads etc. – Hamlet Hakobyan Aug 11 '16 at 22:46
@HamletHakobyan I used to read about this topic for `postgres` as well and things were exactly the same there - no difference. There's even a msg from Tom Lane at postgres mailing list ( https://www.postgresql.org/message-id/11471.1027875769%40sss.pgh.pa.us ) I find his words uncontested - who knows this engine better than he does :) – Kamil Gosciminski Aug 11 '16 at 22:52
3

The 25% is probably caused by cache warming. next! – wildplasser Aug 11 '16 at 22:53
[Count(*) vs Count(1)](http://stackoverflow.com/questions/1221559/count-vs-count1) – sstan Aug 11 '16 at 23:09

2 Answers2