How to Speed Up COUNT(*) on Large Tables in SQL Server

countsql serversql-server-2012

We're using a Vendor App running on SQL Server Enterprise, and it has a rather annoying quirk of executing COUNT statements on the Items table while processing most financial documents (orders, invoices, etc.).

E.g. SELECT COUNT('A') FROM [dbo].[Items] T0

I'm sure that would normally be fine, but there's over 6 million records, and it takes ~400ms to count them all. This can constitute a substantial portion of the overall processing time.

The table already has an extremely narrow NonClustered Index (tinyint, plus Clustered Key) on it which is what SQL is using when it does the Table Scan, so I don't think we can do any better in that regard.

There's a few solutions I'm aware of, which we'd like to avoid if possible:

Indexed View using COUNT_BIG(*)
Use faster Processors – we're using Cloud Servers, and there's very limited options in this regard.
Deleting Records – not really an option in this case.

Do we have any other options to speed this up?

Here's a Gist showing the setup: https://gist.github.com/elvishfiend/5094f120b14f8ecfb325623edcb5f3eb

Best Answer

The indexed view should be among the fastest options, with the lowest maintenance overhead, when implemented optimally.

Modifications are incremental (deltas) as I explain in detail in Indexed View Maintenance in Execution Plans (a full recount is not performed on every base table update); however, you do need to ensure that the delta update parts of the execution plan have efficient access methods (like any query).

It is typically quite simple to identify a missing index from the INSERT/UPDATE/DELETE execution plan. Perhaps you could add an illustrative post-execution (actual) execution plan to your question.

Automatic matching of query text to an indexed view is only available in Enterprise Edition (and equivalents). In other editions, you must use the WITH (NOEXPAND) table hint. There are also good reasons to use NOEXPAND even on Enterprise Edition.

Regarding the demo code: Make sure you specify the hint using WITH (NOEXPAND). The way you have written it, NOEXPAND is parsed as an alias. Note also that only materialized (indexed) views can have a NOEXPAND hint.

If you are unable to add a hint directly, this would be an excellent use of a Plan Guide. A plan guide can also be used to ensure that a query that matches an indexed view (without naming it explicitly) actually uses the indexed view.

Remember that without NOEXPAND on a materialized (indexed) view, SQL Server always expands the view definition at the start of plan compilation. Enterprise Edition may (or may not) match (parts of) a query to an indexed view depending on its assessment of the costs of each option.

Related Q & A:

Related Solutions

Sql-server – Speeding up COUNT(*) – WHERE clause slowing query

Another approach to remove the OR.
You could try this and have 2 indexes on the table to satisfy the WHERE clauses.

SELECT
    COUNT(*)
FROM
    (
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND hidden = 0
    UNION
    SELECT message_id
    FROM messages
    WHERE message_type_id = 1 AND message_id = @message_id
    ) X

PostgreSQL – Count Where Previous Record Exists in Either of Two Tables

One way would be to LEFT JOIN both tables (counting_table itself as well as other_table), and count a COALESCE expression, which will be NULL (and therefore not counted) if neither table has a "previous" record:

SELECT count(COALESCE(o.bigint_col, c1.bigint_col)) AS ct
FROM   counting_table c
LEFT   JOIN other_table o ON o.bigint_col = c.bigint_col - 1
                         AND o.char_col   = c.char_col
LEFT   JOIN counting_table c1 ON c1.bigint_col = c.bigint_col - 1
                             AND c1.char_col   = c.char_col;

The other option would be with two OR'ed EXISTS semi-joins:

SELECT count(*) AS ct
FROM   counting_table c
WHERE EXISTS (
   SELECT 1 FROM other_table o
   WHERE  o.bigint_col = c.bigint_col - 1
   AND    o.char_col   = c.char_col
   )
OR    EXISTS (
   SELECT 1 FROM counting_table c1
   WHERE  c1.bigint_col = c.bigint_col - 1
   AND    c1.char_col   = c.char_col
   );

I prefer the second variant as it also works with multiple "previous" rows matching in one of both related tables - which does not seem to apply to your case, but it's still the safer bet. Also typically a bit faster.

Best Answer

Related Solutions

Sql-server – Speeding up COUNT(*) – WHERE clause slowing query

PostgreSQL – Count Where Previous Record Exists in Either of Two Tables

Related Question