Getting RowCount of Large Table

Question

Post reply

Getting RowCount of Large Table

Jason Messersmith

Right there with Babe

Points: 794
More actions
June 28, 2010 at 1:16 pm

#234431

I have a table with about two billion rows in it, every night we query for the row count. It takes about 5-6mins for it to come back. Here is the query I use:
SELECT SUM(1)
FROM BALEOD2009 (nolock)
WHERE EODDate >= 20081228
I know you can use sys index views to get this also but have read it isnt very accurate. Any suggestions on speeding this up? It is already indexed, I haven't partitioned the table yet though.

Viewing 15 posts - 1 through 15 (of 33 total)

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

hemakumar_p Old Hand Points: 385 More actions · Answer 1

Simply way Through Management studio you ca get the row count.

right click on the table ->properties -> Storage -> General -> Row count.

You will get the row count very quickly through UI.

Regards

Hema.,

Regards
Hema.,

LutzM SSC Guru Points: 107049 More actions · Answer 2

Any particular reason for not using COUNT(*)?

But more interestingly: is there any index available supporting the WHERE clause?

If not, can you change it to benefit from an existing index (e.g. get the corresponding edit: ~~clustered~~ narrow index value for EODDate = 20081228) and use the ~~clustered~~ index column in your WHERE clause?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

lmu92 (6/28/2010)
Any particular reason for not using COUNT(*)?
But more interestingly: is there any index available supporting the WHERE clause?
If not, can you change it to benefit from an existing index (e.g. get the corresponding edit: ~~clustered~~ narrow index value for EODDate = 20081228) and use the ~~clustered~~ index column in your WHERE clause?

And what's the datatype of the EODDate column... INT?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jason Messersmith Right there with Babe Points: 794 More actions · Answer 4

There is no gain with using Count(*), yup everything is running on an index

Jason Messersmith Right there with Babe Points: 794 More actions · Answer 5

EODDate is an int atm, I am working on some tests to see if changing it will help but the date is used as an int thoughout the database. I have another post.

LutzM SSC Guru Points: 107049 More actions · Answer 6

Jason Messersmith (6/28/2010)
There is no gain with using Count(*), yup everything is running on an index

Can you post the create index script for the related index?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jason Messersmith Right there with Babe Points: 794 More actions · Answer 7

lmu92 (6/28/2010)
Jason Messersmith (6/28/2010)
There is no gain with using Count(*), yup everything is running on an index
Can you post the create index script for the related index?

Here is the table:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[BALEOD2009](

[SkuNumber] [bigint] NOT NULL,

[Store] [int] NOT NULL,

[OnHand] [float] NULL,

[RecieptAmtPTD] [float] NULL,

[RecieptAmtYTD] [float] NULL,

[AverageCost] [float] NULL,

[RegCstAmtPTD] [float] NULL,

[RegCstAmtYTD] [float] NULL,

[AdCstAmtPTD] [float] NULL,

[AdCstAmtYTD] [float] NULL,

[AverageCost1] [float] NULL,

[LandedCost] [float] NULL,

[IsType] [varchar](50) NULL,

[IATRB4] [varchar](50) NULL,

[EODDate] [int] NOT NULL,

[ProcessDate] [int] NULL,

[CounterType] [varchar](50) NULL,

[RegularRetail] [float] NULL,

[PromoRetail] [float] NULL,

[IATRB1] [varchar](50) NULL,

[IFINLN] [varchar](50) NULL,

[INTRANSIT] [float] NULL,

[StoreSelect] [varchar](50) NULL,

[LastUpdateDate] [datetime] NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[BALEOD2009] ADD CONSTRAINT [DF_BALEOD20091_LastUpdateDate] DEFAULT (getdate()) FOR [LastUpdateDate]

GO

The index:

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

(

[EODDate] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

The plan is also attached.

Lamprey13 SSCertifiable Points: 6190 More actions · Answer 8

Is the table a heap or did you just omit the clustered index?

Jason Messersmith Right there with Babe Points: 794 More actions · Answer 9

Lamprey13 (6/28/2010)
Is the table a heap or did you just omit the clustered index?

The other index on it is clustered,

CREATE UNIQUE CLUSTERED INDEX [Idx_BALEOD20091SkuStoreEOD] ON [dbo].[BALEOD2009]

(

[EODDate] ASC,

[Store] ASC,

[SkuNumber] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

Jason Messersmith (6/28/2010)
EODDate is an int atm, I am working on some tests to see if changing it will help but the date is used as an int thoughout the database. I have another post.

Except for the fact that I'm generally against using INT to store ISO dates, there's no need to change it here because it won't help for this particular task. I just wanted to make sure we didn't have any implicit conversions going on in the table side.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 11

@jason,

The SQLPlan you posted seems to be an "estimated" plan... can you include an "Actual" plan?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jason Messersmith Right there with Babe Points: 794 More actions · Answer 12

Jeff Moden (6/28/2010)
@Jason,
The SQLPlan you posted seems to be an "estimated" plan... can you include an "Actual" plan?

done.

LutzM SSC Guru Points: 107049 More actions · Answer 13

Jeff Moden (6/28/2010)
Except for the fact that I'm generally against using INT to store ISO dates, there's no need to change it here because it won't help for this particular task. I just wanted to make sure we didn't have any implicit conversions going on in the table side.

I'm not sure if in this case storing EODDate as SQL2008 DATE format would help since it would "shrink" the index pages to be scanned by 25% (4 byte for INT vs. 3 byte for DATE). Maybe even based on a persisted computed column, if the additional space can be tolerated...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jason Messersmith Right there with Babe Points: 794 More actions · Answer 14

lmu92 (6/28/2010)
Jeff Moden (6/28/2010)
Except for the fact that I'm generally against using INT to store ISO dates, there's no need to change it here because it won't help for this particular task. I just wanted to make sure we didn't have any implicit conversions going on in the table side.
I'm not sure if in this case storing EODDate as SQL2008 DATE format would help since it would "shrink" the index pages to be scanned by 25% (4 byte for INT vs. 3 byte for DATE). Maybe even based on a persisted computed column, if the additional space can be tolerated...

The indexes are 40gb, however the table its self is 330+gb