What can speed up a SQL count query?

Question

When doing a count (aggregate) SQL query, what can speed up the execution time in these 3 database systems? I'm sure many things could speed it up (hardware for one), but I'm just a novice DBA, so I'm sure I'll be getting a few answers here. I migrated about 157 million rows to a SQL Server database, and this query is taking forever. But in my source Netezza database, it takes seconds.

For example:

Netezza 6:

SELECT COUNT(*) FROM DATABASENAME..MYTABLE

Oracle 11g:

SELECT COUNT(*) FROM MYTABLE

SQL Server 2012:

SELECT COUNT(*) FROM DATABASENAME.[dbo].[MYTABLE]

Answer 1

Netezza is an appliance that is designed to excel at large table scans, so that's why you're getting such fast results on that system.

For your SQL Server, you can greatly speed up the row count by querying from the sys.dm_db_partition_stats DMV.

SELECT s.name AS [Schema], o.name AS [Table], SUM(p.row_count) AS [RowCount]
FROM sys.dm_db_partition_stats p JOIN sys.objects o
ON p.object_id = o.object_id JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE p.index_id < 2
AND o.object_id = object_id('MyTable')
GROUP BY o.name, s.name;

In a high transaction environment, this DMV is not guaranteed to be 100% accurate. But from your question, it sounds like you are just doing row counts to verify each table after your migration, so this query should work for you.

Answer 2

Here's a SQL Server solution that uses COUNT_BIG inside an indexed view. This will get you a transactionally-consistent count without the overhead of big table or index scans, and without the need for the storage required for the latter:

CREATE TABLE [dbo].[MyTable](id int);
GO

CREATE VIEW [dbo].[MyTableRowCount]
    WITH SCHEMABINDING
AS

    SELECT
        COUNT_BIG(*) AS TableRowCount
        FROM [dbo].[MyTable];
GO

CREATE UNIQUE CLUSTERED INDEX IX_MyTableRowCount
    ON [dbo].[MyTableRowCount](TableRowCount);
GO

SELECT
    TableRowCount
    FROM [dbo].[MyTableRowCount] WITH(NOEXPAND);

This will require a single initial scan (no getting away from this), and add a bit of overhead to incremental table data manipulations. If you're doing big operations with lots of data (as opposed to many small operations), I think the overhead on changes should be negligible.

Answer 3

In Oracle, a binary tree index on a NOT NULL column can be used to answer a COUNT(*). It will be faster in most cases than a FULL TABLE SCAN because indexes are usually smaller than their base table.

However, a regular binary tree index will still be huge with 157 Mrows. If your table is not updated concurrently (ie. only batch load process), then you might want to use a bitmap index instead.

The smallest bitmap index would be something like this:

CREATE BITMAP INDEX ix ON your_table(NULL);

Null entries are taken into account by a bitmap index. The resulting index will be tiny (20-30 8k blocks per million row) compared to either a regular binary tree index or the base table.

The resulting plan should show the following operations:

----------------------------------------------
| Id  | Operation                     | Name | 
----------------------------------------------
|   0 | SELECT STATEMENT              |      |
|   1 |  SORT AGGREGATE               |      |
|   2 |   BITMAP CONVERSION COUNT     |      |
|   3 |    BITMAP INDEX FAST FULL SCAN| IX   |
----------------------------------------------

If your table is updated concurrently, a bitmap index with a unique value will be a point of contention and shouldn't be used.

Answer 4

In Oracle, simple count query is often executed by scanning an index instead of whole table. The index must be bitmap index or defined on a column with NOT NULL constraint. For more complex queries that require full table scan, you could use parallel query.

To enable parallel query (Enterprise Edition required), you can use optimizer hint:

select /*+ PARALLEL(mytable, 12) */ count(*) from mytable;

Or enable parallel query for all queries on the table:

alter table mytable parallel 12;