May 12, 2009 at 7:18 am
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
May 12, 2009 at 7:22 am
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
May 12, 2009 at 7:37 am
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?
May 12, 2009 at 8:03 am
anyone? 🙁
does the DBCC UPDATEUSAGE have to run everytime i do a count from my table using sys.indexes
May 12, 2009 at 8:31 am
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????
May 12, 2009 at 8:31 am
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
May 12, 2009 at 8:35 am
i need an accurate count
May 12, 2009 at 8:52 am
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.... 🙁
May 12, 2009 at 8:57 am
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
May 12, 2009 at 8:58 am
ah well, guess im stuck with this proc.
May 12, 2009 at 5:48 pm
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
May 12, 2009 at 11:52 pm
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 🙂
May 13, 2009 at 12:11 am
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??
May 13, 2009 at 6:03 am
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
May 13, 2009 at 6:07 am
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