Counting Records

  • Hi everyone, im doing a simple count but i dont understand why its taking this long.

    is there anyway i can make it faster? the column being counted is a clustered index

    Select Count(EntityIdentifierComposite) as Count from EntityCollection

    (1 row(s) affected)

    Table 'EntityCollection'. Scan count 9, logical reads 311218, physical reads 21172, read-ahead reads 251488, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    (1 row(s) affected)

    SQL Server Execution Times:

    CPU time = 1687 ms, elapsed time = 61394 ms.

    SQL Server parse and compile time:

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

    SQL Server Execution Times:

    CPU time = 0 ms, elapsed time = 1

  • this is what the Client Statistics looks like

    Client Execution Time12:26:22

    Query Profile Statistics

    Number of INSERT, DELETE and UPDATE statements00.0000

    Rows affected by INSERT, DELETE, or UPDATE statements00.0000

    Number of SELECT statements 44.0000

    Rows returned by SELECT statements22.0000

    Number of transactions 00.0000

    Network Statistics

    Number of server roundtrips44.0000

    TDS packets sent from client44.0000

    TDS packets received from server66.0000

    Bytes sent from client998998.0000

    Bytes received from server1263612636.0000

    Time Statistics

    Client processing time1515.0000

    Total execution time6140761407.0000

    Wait time on server replies6139261392.0000

  • seems like i found something that may help me retrieve the count much quicker

    SELECT rows as Count FROM sysindexes WHERE id = OBJECT_ID('[EntityCollection]') AND indid < 2

    what are the advantages and disadvantages of using this statement, ive read that one needs to use DBCC UDPATEUSAGE in conjuction with the above statement to get an accurate count.

    can someone please explain?

  • anyone? 🙁

    does the DBCC UPDATEUSAGE have to run everytime i do a count from my table using sys.indexes

  • i've run the following

    DBCC UPDATEUSAGE ('Wallet0000', 'EntityCollection');

    GO

    SELECT rows as Count FROM sysindexes WHERE id = OBJECT_ID('[EntityCollection]') AND indid < 2

    but i still get a different count to

    select count(*) from EntityCollection

    is there no way i can increase the performance of this count statement????

  • sql_prodigy (5/12/2009)


    anyone? 🙁

    does the DBCC UPDATEUSAGE have to run everytime i do a count from my table using sys.indexes

    Depends if your count needs to be 100% accurate, or if you need an idea of the rows currently in the table. When you run the DBCC UpdateUsage, it will be 100% accurate, but the number of rows you get from the sys.Indexes are never really far from the reality, I don't think it can hit 1% difference, but that makes it a lot on larger tables.

    Here's the query I usually use for that kind of operation.

    SELECT object_NAME(p.object_ID) as [RowCount],

    p.Rows

    FROM sys.partitions P

    INNER JOIN sys.objects O

    ON P.object_ID = O.Object_ID

    AND O.is_ms_shipped = 0 -- Only tables you created

    WHERE P.Index_ID IN (0,1)

    ORDER BY p.rows DESC

    Cheers,

    J-F

  • i need an accurate count

  • i still get an inaccurate count of rows in my table using the following statement

    SELECT o.NAME, p.OBJECT_ID, o.type_desc, p.rows

    FROM sys.objects o

    INNER JOIN sys.partitions p ON o.OBJECT_ID = p.OBJECT_ID

    WHERE

    p.OBJECT_ID = 117575457

    can anyone please shed some light on this?

    it seems as there is no way around not using the count function, guess im stuck with this bad performing proc.... 🙁

  • If your count needs to be accurate, then you will either need to run the DBCC UpdateUsage ('DatabaseName','TableName') before you query the sys.partitions view, or run the Select * against the table.

    Maybe, on some larger tables, you might see it is longer to run the DBCC updateUsage, then querying the table directly, I ran it on a 3 million rows table, and the Update took 25 seconds, and select * from the table took 12 seconds.

    It's up to you, and depends on how the indexes are "not updated".

    Cheers,

    J-F

  • ah well, guess im stuck with this proc.

  • Prodigy, what else is going on in your proc? I see a simple Select Count statement but you post that it's taking 9 scans of the table to do it? That can't be right. Please post up your code, including where you have the "set statistics io on" and "set statistics io off" statements.

    Or just try running this and post the results back:

    set statistics io on;

    set statistics time on;

    Select Count(EntityIdentifierComposite) as Count from EntityCollection

    set statistics time off;

    set statistics io off;

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • that is the exact query i run, nevertheless i'll post the same results again.

    what does scan count mean?

    (1 row(s) affected)

    Table 'EntityCollection'. Scan count 9, logical reads 311554, physical reads 21061, read-ahead reads 252192, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

    SQL Server Execution Times:

    CPU time = 1592 ms, elapsed time = 60688 ms.

    i did significantly improve the performance though, i added a non clustered index on a smaller column 🙂

  • ok. Scan Count - is the no of times a table is referenced in a query. i.e. How many times the table was accessed.

    so with that in mind, why would my simple count query scan/access the particular table 9 times??

  • sql_prodigy (5/13/2009)


    ok. Scan Count - is the no of times a table is referenced in a query. i.e. How many times the table was accessed.

    so with that in mind, why would my simple count query scan/access the particular table 9 times??

    Probably because of "Parallelism".

    Can you provide the "Execution Plan" as an .sqlplan file to see the actual indexes being used?

    --Ramesh


  • SELECT rows as Count FROM sysindexes WHERE id = OBJECT_ID('[EntityCollection]') AND indid < 2

Viewing 15 posts - 1 through 15 (of 32 total)

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