Getting RowCount of Large Table

Question

Getting RowCount of Large Table

Viewing 4 posts - 31 through 33 (of 33 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Here's another direction. Parsing and counting 9GB of data is always going to be more of an intensive tasks which you'll need to throw hardware at eventually.

But after counting the rows, what do you need to do with that data for that process? I'm assuming here that any data load, or reporting or cubes will take FAR more than 6 minutes. So I kind of fail to see the issue / and the bigger picture here.

kchant Ten Centuries Points: 1231 More actions · Answer 2

You could try running DBCC UPDATEUSAGE first and then using the system views to get an accurate row count, if you're using your original query with the WHERE clause still then it may be worth creating a Filtered Index on the EODDate column, e.g.

CREATE NONCLUSTERED INDEX [Idx_BALEOD20091EODDate] ON [dbo].[BALEOD2009]

(

[EODDate] ASC

)WHERE EODDate >= 20081228

GO

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

kchant (7/1/2010)
You could try running DBCC UPDATEUSAGE first and then using the system views to get an accurate row count, if you're using your original query with the WHERE clause still then it may be worth creating a Filtered Index on the EODDate column, e.g.
CREATE NONCLUSTERED INDEX [Idx_BALEOD20091EODDate] ON [dbo].[BALEOD2009]
(
[EODDate] ASC
)WHERE EODDate >= 20081228
GO

Make sure you upgraded sql server recently for that one because there was a bug in the engine and the index was not used...

Alos that assumes the where actually filters something!!!

kchant Ten Centuries Points: 1231 More actions · Answer 4

Ninja's_RGR'us (7/1/2010)
kchant (7/1/2010)
You could try running DBCC UPDATEUSAGE first and then using the system views to get an accurate row count, if you're using your original query with the WHERE clause still then it may be worth creating a Filtered Index on the EODDate column, e.g.
CREATE NONCLUSTERED INDEX [Idx_BALEOD20091EODDate] ON [dbo].[BALEOD2009]
(
[EODDate] ASC
)WHERE EODDate >= 20081228
GO
Make sure you upgraded sql server recently for that one because there was a bug in the engine and the index was not used...
Alos that assumes the where actually filters something!!!

Ah, I wasn't aware of that bug.

Anyway, that's a good point about the where clause, and if it's not filtering anything then maybe worth considering removing it. On a side not have you ran DBCC SHOWCONTIG or looked at sys.dm_db_index_physical_stats to check how fragmented the indexes currently are (if you're going to run DBCC SHOWCONTIG probably a good idea to do it outside of production hours)?