Home > Uncategorized > A Quicker Alternative to SELECT COUNT(*)

A Quicker Alternative to SELECT COUNT(*)

Right now I am working on a process to transfer a single table between databases. There are over 36 million rows in the source table, and as a result I am batching the inserts in a loop. I need the upper limit of my loop, which is equal to the number of rows in the source table. Usually, I would set the @rowlimit variable using a line of code similar to this:

SELECT @rowlimit = COUNT(*) FROM sourcetable

In the case of the 36 million rows, the SELECT COUNT(*) was taking far too long. Fortunately, there is an alternative in the sp_spaceused system stored procedure. Where the SELECT COUNT(*) operation was taking up to 20 minutes, the following code returns in seconds.

USE databasename
GO

CREATE TABLE #spaceused
(name NVARCHAR(128)
,rows CHAR(11)
,reserved VARCHAR(18)
,data VARCHAR(18)
,index_size VARCHAR(18)
,unused VARCHAR(18))

DECLARE @rowlimit BIGINT

INSERT INTO #spaceused
EXEC sp_spaceused tablename

SELECT @rowlimit = rows FROM #spaceused
WHERE name = 'tablename'

SELECT @rowlimit 

DROP TABLE #spaceused

I hope you find this useful.

Categories: Uncategorized
  1. January 8, 2013 at 07:05

    Why not SELECT @rowlimit = SUM(row_count) FROM sys.dm_db_partition_stats WHERE [object_id] = OBJECT_ID(‘dbo.sourcetable’) AND index_id IN (0,1); ?

    • January 8, 2013 at 09:45

      Aaron,

      Because I hadn’t figured that one out. Perhaps I should hire you as a code editor. 😀 Seriously, though I appreciate the insight. Outside of avoiding the temp table, is there an appreciable performance difference between the two?

  2. January 8, 2013 at 09:47

    I expect the sp_spaceused version to be slower, even without the temp table, since it’s going to be doing a lot of additional stuff you’re not interested in if all you’re looking for is a row count.

  3. January 8, 2013 at 09:52

    That said, slower is relative. sp_spaceused is much, much faster than COUNT(*). Using the DMV is going to be just marginally faster than that.

  1. January 25, 2013 at 05:03

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: