SQL Server Performance

Count rows in table in flash

Discussion in 'Contribute Your Performance and Clustering Tips' started by dineshasanka, Apr 7, 2005.

  1. dineshasanka Moderator

    When counting number of rows in table it will take awful lot of time a sit has to scan through the table. For example I have a table with rows 18346967 and if I execute



    Select Count(*) from table1

    It will take 32 seconds to give me the result.

    But if I run


    select rows from sysindexes where id = OBJECT_ID(table1) and indid < 2

    It will take less than one second.
    However problem you have this with you cant get the group count. You can only use when you are counting all the records.
    dineshasanka, Apr 7, 2005
    #2

  2. ranjitjain New Member

    When i saw this query for the first time here in SQL-Server-Permonce.com under query hints section even i was surprised and when i ran the query on my tables it was flashy and ive added this query in my favourites.
    ranjitjain, Apr 7, 2005
    #2

  3. FrankKalis Moderator

    There is only one thing to keep in mind here. While the results are usually correct, there is no guarantee that they always are. The only reliable way is unfortunately SELECT COUNT(*).

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
    FrankKalis, Apr 8, 2005
    #2

  4. ChrisFretwell New Member

    The query from the index table is also the same result that will come back in a rowcount from sp_spacesused, and it of coures carries the same warning as Frank mentioned.

    Chris
    ChrisFretwell, Apr 8, 2005
    #2

  5. dineshasanka Moderator

    Frank,
    Can u share the instances where this is failing, please

    quote:Originally posted by FrankKalis

    There is only one thing to keep in mind here. While the results are usually correct, there is no guarantee that they always are. The only reliable way is unfortunately SELECT COUNT(*).

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de

    dineshasanka, Apr 10, 2005
    #2

  6. FrankKalis Moderator

    Failing is not the correct term here. It is usually accurate, but just not guaranteed to. If you experience inconsistencies here, DBCC UPDATEUSAGE will correct them. I think when you perform large INSERT or DELETE operations this number can get out of synch.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de
    FrankKalis, Apr 11, 2005
    #2

  7. Argyle New Member

    True. Do a DBCC UPDATEUSAGE(0) on one of your "heavy" databases and you might be surpried by how much data is out of sync in sysindexes.
    Argyle, Apr 11, 2005
    #2

  8. dineshasanka Moderator

    Thankx Frank

    quote:Originally posted by FrankKalis

    Failing is not the correct term here. It is usually accurate, but just not guaranteed to. If you experience inconsistencies here, DBCC UPDATEUSAGE will correct them. I think when you perform large INSERT or DELETE operations this number can get out of synch.

    --
    Frank Kalis
    SQL Server MVP
    http://www.insidesql.de

    dineshasanka, Apr 11, 2005
    #2

  9. ranjitjain New Member

    Hi dineshashanka,
    I've a table say table in which i did bulk insert and then i ran
    select rows from sysindexes where id = OBJECT_ID(table1) and indid < 2
    it gave me output as 111785, and when i ran EM and checked the properties of that table, even that shows me the same rows 111785.
    but when i ran select count(*) its giving me output 112209, so i'm facing the real time problem here.
    now ill run dbcc updateusage and check what happens.
    ranjitjain, Apr 11, 2005
    #2

  10. ranjitjain New Member

    friends i ran dbcc updateusage(0) as said by argyle and then i ran
    select rows from sysindexes where id = OBJECT_ID(table1) and indid < 2
    which is still giving me 111785 but the count is 112209.
    what should i do to get the correct no. of rows by running the above flash query.
    ranjitjain, Apr 11, 2005
    #2

  11. Madhivanan Moderator

    Madhivanan, Apr 27, 2005
    #2

  12. ranjitjain New Member

    ranjitjain, Apr 27, 2005
    #2

  13. Artur New Member

    It is also worth to mention to filter counts with user tables and not to count indexes. Please find the syntax at Malcan - Rows counts - getting quickly, comparing databases. You may also find there the join query that gives you the difference of rows per table between two databases. Easy and good to remember
    Artur, Dec 22, 2008
    #3

Share This Page