Counting Records

  • sql_prodigy

    SSCrazy

    Points: 2254

    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

  • sql_prodigy

    SSCrazy

    Points: 2254

    this is what the Client Statistics looks like

    Client Execution Time 12:26:22

    Query Profile Statistics

    Number of INSERT, DELETE and UPDATE statements 0 0.0000

    Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000

    Number of SELECT statements 4 4.0000

    Rows returned by SELECT statements 2 2.0000

    Number of transactions 0 0.0000

    Network Statistics

    Number of server roundtrips 4 4.0000

    TDS packets sent from client 4 4.0000

    TDS packets received from server 6 6.0000

    Bytes sent from client 998 998.0000

    Bytes received from server 12636 12636.0000

    Time Statistics

    Client processing time 15 15.0000

    Total execution time 61407 61407.0000

    Wait time on server replies 61392 61392.0000

  • sql_prodigy

    SSCrazy

    Points: 2254

    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?

  • sql_prodigy

    SSCrazy

    Points: 2254

    anyone? 🙁

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

  • sql_prodigy

    SSCrazy

    Points: 2254

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

  • J-F Bergeron

    SSCrazy Eights

    Points: 8593

    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

  • sql_prodigy

    SSCrazy

    Points: 2254

    i need an accurate count

  • sql_prodigy

    SSCrazy

    Points: 2254

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

  • J-F Bergeron

    SSCrazy Eights

    Points: 8593

    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

  • sql_prodigy

    SSCrazy

    Points: 2254

    ah well, guess im stuck with this proc.

  • The Dixie Flatline

    SSC Guru

    Points: 53197

    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

  • sql_prodigy

    SSCrazy

    Points: 2254

    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 🙂

  • sql_prodigy

    SSCrazy

    Points: 2254

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

  • Ramesh Saive

    SSC-Insane

    Points: 24275

    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


  • Sundar K. Urs

    SSC Veteran

    Points: 278

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

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

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