Data Warehousing, BI and Data Science

12 November 2010

SELECT COUNT(*) FOR BIG TABLE

Filed under: Analysis Services — Vincent Rainardi @ 7:39 pm

To get the number or rows in a table we usually do:


SELECT COUNT(*) FROM VERY_BIG_TABLE

For a small table (<100k rows) it’s fine but if it’s a very big table it takes a long time.

A table with 250 million rows , unpartitioned, no index, 40 columns: it takes 2 mins 18 seconds to do select count(*). (SQL Server with dual processors, 32GB RAM, single RAID5).

This one only takes 1 second:


select sum(P.rows) from sys.partitions P with (nolock)

join sys.tables T with (nolock) on P.object_id = T.object_id

where T.Name = 'VERY_BIG_TABLE' and index_id = 1

Would be very useful to memorise that one! As in data warehousing we deal with lots of very big tables.

Well the “nolock” hint is totally optional. I was just being too careful there. No need for it really as they are system tables which are not modifiable by users.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: