Getting RowCount of Large Table

Question

Getting RowCount of Large Table

Viewing 15 posts - 16 through 30 (of 33 total)

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

old account - will s SSC Veteran Points: 243 More actions · Answer 1

Have you tried forcing it to used the clustered index with a query hint?

I know that index is bigger, and SQL is probably not choosing it for a good reason, but I just wondered if the clustered index would scan faster since it's physically contiguous.

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 2

ya i thought of that about 8mins ago, the query is still running.

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

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

Now THAT's interesting... although an INDEX SEEK is listed, no predicates are listed and the entire table is being scanned. Of course, even seeking (turns into a range scan in this case) a 2 billion row table is going to be a bit slow. Do ALL the rows in the table meet the date criteria? In other words, does the table contain only data that meets the date criteria?

--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)

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 4

Jeff Moden (6/28/2010)
Jeff Moden (6/28/2010)
@Jason,
The SQLPlan you posted seems to be an "estimated" plan... can you include an "Actual" plan?
Now THAT's interesting... although an INDEX SEEK is listed, no predicates are listed and the entire table is being scanned. Of course, even seeking (turns into a range scan in this case) a 2 billion row table is going to be a bit slow. Do ALL the rows in the table meet the date criteria? In other words, does the table contain only data that meets the date criteria?

I think you might be right about that, it appears the where clause on this is pointless at least on the production box, It might have been left over from development. I'll try it without it and see how long it takes.

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 5

Jason Messersmith

SSC Eights!

Points: 824

June 28, 2010 at 4:04 pm

#1186092

Ya it still took about 6mins. There is the plan.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Why not try this:

select max(tbl.rn) from

(

select row_number() over(order by [SkuNumber]) rn from BALEOD2009

) tbl

It might produce the "baddest" ever result, but u can just try it out..:w00t:

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 7

or:

Declare @r_Count1 bigint

select top 1 @r_Count1 = tbl.rn from

(

select row_number() over(order by (select 0)) rn

from MyTable

) tbl

order by tbl.rn desc

SELECT @r_Count1

I put COUNT(*) and ROW_NUMBER() to test and found count(*) is way above ROW_NUMBER() in performance..

************* COUNT(*)

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 3920 ms, elapsed time = 1095 ms.

************* row_number()

SQL Server Execution Times:

CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:

CPU time = 8578 ms, elapsed time = 8380 ms.

Hmmm.....

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 8

Jason Messersmith

SSC Eights!

Points: 824

June 29, 2010 at 8:13 am

#1186409

I didnt have any luck with these queries.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 9

Jason Messersmith (6/29/2010)
I didnt have any luck with these queries.

:doze: i dint quite get u , Jason ; pls elaborate !

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 10

Have you ever updated statistics on this table? 😀

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 11

ColdCoffee (6/29/2010)
Jason Messersmith (6/29/2010)
I didnt have any luck with these queries.
:doze: i dint quite get u , Jason ; pls elaborate !

they ran for about 10mins and I shut them down.

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 12

Eugene Elutin (6/29/2010)
Have you ever updated statistics on this table? 😀

That's a good call, I'll give that a try and let you know.

Stefan_G SSCertifiable Points: 6609 More actions · Answer 13

I would probably use:

select sum(rows)

from sys.partitions

where

index_id <= 1

and

object_id = object_id('[dbo].[BALEOD2009]')

Even if Microsoft has described the rows field as "approximate number of rows" I still have not seen a single case when this value has been wrong compared to COUNT(*)

I suppose that there might be some problem if you make this query while there are insert or delete operations in progress, but if you do it on a silent table the result is in my experience accurate.

You can test it for a while in your environment.

Use the query above and your standard COUNT(*) and compare the results. If there are no differences for a week maybe you can start trusting the values from sys.partitions.

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 14

Jason Messersmith (6/29/2010)
Eugene Elutin (6/29/2010)
Have you ever updated statistics on this table? 😀
That's a good call, I'll give that a try and let you know.

Have you tried?

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Jason Messersmith SSC Eights! Points: 824 More actions · Answer 15

No dice on the updating of stats, It still takes about the same amount of time. 5-6mins.

I'm going to start logging from a few system views and see if those are accurate enough like Stefan_G said.