Sql-server – What can speed up a SQL count query

countnetezzaperformancequery-performancesql server

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]

Best Answer

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.

Related Solutions

Mysql – How to improve query count execution with theSql replicate

Give this a try:

Add this composite index to user_activity: INDEX(user_id, action). Then

SELECT SUM(x.ct), u.user_type, x.action
    FROM (
        SELECT user_id, action, COUNT(*) AS ct
            FROM user_activity
            GROUP BY user_id, action
         ) x
    JOIN user u  ON x.user_id = u.id
    GROUP BY x.action, u.user_type;

Mysql – Is 3 minutes to join a 50 million table too much

FROM ( SELECT ... ) JOIN ( SELECT ... )

does not optimize well. Think of a better way to write the query. If that fails, put one of the subqueries into a TEMPORARY TABLE and add an index to it.

Consider using the datatype DATE, not INT, for dates.

OR is a performance killer (because it prevents use of an index). Consider other ways to deal with IS NULL.

More

If order_id is unique in orders, then get rid if id and user order_id as the PRIMARY KEY. (Plus any changes needed to other tables?) Lookup by PK is faster than lookup by secondary key.

If the information in some of these tables is "static" (written once, but not updated or deleted), then consider building and maintaining a "Summary table".

For doing this query 13 times for 13 categories, it may be faster to do it once, with a GROUP BY category_id.

Turning OR into UNION may speed things up, especially if it lets a better index be used.

Best Answer

Related Solutions

Mysql – How to improve query count execution with theSql replicate

Mysql – Is 3 minutes to join a 50 million table too much

Related Question