Select count(*) from table1 is taking time

Question

Select count(*) from table1 is taking time

aprathour.89

Default port

Points: 1495
More actions
August 31, 2019 at 11:45 am

#3676021

Hi All,
Select count(*) from table1 is taking around 5 mins.
table is having 4035938 rows.
can anyone suggest what may cause row count to slow and how it can be optimized ?
Thanks.

-----------------
Aditya Rathour
SQL DBA
Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 1

aprathour.89 wrote:

Hi All,
Select count(*) from table1 is taking around 5 mins.
table is having 4035938 rows.
can anyone suggest what may cause row count to slow and how it can be optimized ?
Thanks.

It cannot be optimized because you've requested that that SELECT return all columns and all rows contained in the table of more than 4 million rows and you're likely trying to also display all that on the screen.

Figure out what you really need from the table by column(s) and filter for only the rows you need. If that turns out to be too slow, then we can worry about some form of optimization.

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

aprathour.89 Default port Points: 1495 More actions · Answer 2

Thanks Jeff for your reply.

query is not returning all data , its just returning row count .

I assume that the query should return the row count quickly , not sure why its taking time.

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

dbgaragedays Old Hand Points: 358 More actions · Answer 3

Are there any indexes on the table? what is the execution plan ? If the table does not have any non-clustered index, it will have to scan the table.

You could try this method

select sum (spart.rows)

from sys.partitions spart

where spart.object_id = object_id('Your table name here')

and spart.index_id < 2

I don't think this even requires an index. But you should probably have a clustered index on it anyways...

-Jason

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 4

aprathour.89 wrote:

Thanks Jeff for your reply.
query is not returning all data , its just returning row count .
I assume that the query should return the row count quickly , not sure why its taking time.

I should never respond to a post without the proper amount of caffeine in my system. I totally missed the COUNT and only saw the "*".

The reason why it doesn't return the count as quickly as you like is because it does have to traverse the whole table. In the absence of a narrow index, it will be the whole table or Clustered Index (or HEAP, if there isn't one) and that's why Jason asked if you had any indexes on the table.

He also suggests examining the sys.partitions and his good code will do the job nearly instantly.

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

aprathour.89 Default port Points: 1495 More actions · Answer 5

Table wasn't having any index and select count(*) was doing table scan.

After adding the non-clustered index to table , select count(*) is giving result within seconds.

Thanks Jeff and Jason for your valuable reply and helping out me.

-----------------
Aditya Rathour
SQL DBA

Not Everything that is faced can be changed,
but nothing can be changed until it is faced.

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 6

Rather than check the table, I check the main index on the table to see how many rows it has. In my experience, this is much faster than checking the whole table.

SELECTSUM(rows)
FROMsys.partitions
WHEREobject_id = OBJECT_ID('Person') AND index_id IN ( 0, 1 )

Each table will have only one index with "index_id" 0 or 1, so the query limits to those index types. If using multiple partitions, there may be more than one record for the index type, so the SUM is necessary to get all rows in a single result.

I've never seen this query to be wrong. Does anyone else know cases where the returned value will be incorrect?

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 7

fahey.jonathan wrote:

Rather than check the table, I check the main index on the table to see how many rows it has. In my experience, this is much faster than checking the whole table.
SELECTSUM(rows)
FROMsys.partitions
WHEREobject_id = OBJECT_ID('Person') AND index_id IN ( 0, 1 )
Each table will have only one index with "index_id" 0 or 1, so the query limits to those index types. If using multiple partitions, there may be more than one record for the index type, so the SUM is necessary to get all rows in a single result.
I've never seen this query to be wrong. Does anyone else know cases where the returned value will be incorrect?

Your code is nearly identical to the code that dbgaragedays posted above and, no, I've not seen it be any more wrong that COUNT(*) can be on a busy system but, as you know, it's one of the fastest ways to get a count from some incredibly large tables.

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

fahey.jonathan Hall of Fame Points: 3617 More actions · Answer 8

Jeff: Thanks for pointing out dbgaragedays's post; I skipped right over it.

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 9

fahey.jonathan wrote:

Jeff: Thanks for pointing out dbgaragedays's post; I skipped right over it.

To be sure, I meant it as a compliment to the both of you. The two posts serve as mutual confirmation of great method to get row counts. I also wanted to confirm that I've found it to be as accurate as COUNT(*), which is also accurate only at the instant that it's taken.

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