Post reply

Can''t select count(*) from large table

  • December 28, 2005 at 2:54 pm

    #170210

    I have a table with 2+ bln records. Using sp_spaceused or select rows from sysindexes does not return right value, since usage has not been updated for a long time on this table.

     

    So the only way I have is too select count(*) from table. But it returns an error cannot convert to integer (obviously that value is bigint). Same thing when I run:

    select

    convert(bigint,count(*)) from tbl

    Msg 8115, Level 16, State 2, Line 1

    Arithmetic overflow error converting expression to data type int.

     

    This happen in both SQLServer 2000 and 2005. Can anybody suggest something that will really count the number of records ?

     

    Thanks

     

  • December 28, 2005 at 3:09 pm

    #611806

    try count_big() to return a big integer.

    HTH

    J.Y

  • December 29, 2005 at 8:21 am

    #611918

    try

    select rows

    FROM sysindexes WHERE indid IN(1,2,255)

    and object_name(id) = 'TableName'

  • December 29, 2005 at 8:49 am

    #611930

    select rows

    FROM sysindexes WHERE indid IN(1,2,255)

    and object_name(id) = 'TableName'

    This is usually a good guess at the number of rows, but there's no guarantee that the reported number is correct. If you need it to be accurate, there's no way around COUNT() or COUNT_BIG().

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 29, 2005 at 9:15 am

    #611941

    Try using your PK instead of * in the count function. It should reduce the time needed for execution by a great amount. If the table has multiple indexes then use the narrowest index.

    RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

  • December 29, 2005 at 11:05 am

    #611996

    quote: since usage has not been updated for a long time on this table.

    So why don't you update the usage?

    -SQLBill

  • December 29, 2005 at 12:46 pm

    #612028

    How about "Select count(1)"

    instead of *

  • December 30, 2005 at 2:36 am

    #612095

    Couple of points, why don't you run update usage? Then you'll have the right results. Also using a COUNT(*) isn't as bad as most poeple say, MS have actually build some inteligence into it and if there is a primary key it will count based on that so using COUNT(1) isn't going to be any faster.

  • December 30, 2005 at 5:06 am

    #612099

    and if there is a primary key it will count based on that

    That's slightly incorrect. COUNT(*) will scan the narrowest available index on that table. This is not necessarily the PK.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • December 30, 2005 at 7:52 am

    #612129

    Basically the Count(*) wont be any faster as long you have an index on it. That's it.

    Use Count_big, if the number of row is high.

    Run updateusage regularry because this is the fastes way to tell you APPROXIMATLY the nomber of rows within your table (taken from the sysindexes table btw)



    Bye
    Gabor

  • December 30, 2005 at 8:27 am

    #612149

    Thanks to all for your responses and tips.

    The reason why I rarely run DBCC UPDATEUSAGE is because it takes about an hour to run - ut is very large table, 4+ bln records, and I don't have such large time window on production to do this. But anyway, running select count_big(*) is even longer.

    In most cases I use Select rows from sysindexes, it's good and quick when usage is updated. BTW, here I have another question: what the difference between rowcnt and rows fields in sysindexes table ? In most cases they return same number, but sometimes (when the usage is not updated) they return slightly different values.

    select rowcnt, rows from sysindexes

    where rowcnt<> rows

    Thanks,

    Mark

     

  • December 30, 2005 at 10:35 am

    #612192

    rowcnt is the one that you should be using because:

    1. rowcnt is bigint, (rows is only int)

    2. rows is provided only for backward compatibility

     

    Cheers,

     


    * Noel

Viewing 12 posts - 1 through 11 (of 11 total)

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