Select count(*) from table1 is taking time

  • 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.

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

  • 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.

  • 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

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

  • 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.

  • 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?

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

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

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

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

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