未编码的 Count(*) 与系统表

2021-12-30 00:00:00 optimization count sql-server

我的一个同事给我看的比，要数一个表的记录，如下视图:

A collegue of mine show me than, to count a record of a table, the following view:

CREATE VIEW [SPR].[TablesCount]

AS

SELECT     s.name cSchema,
           t.name cTable,
           sum(p.rows) eRowCount
FROM       sys.tables t
INNER JOIN sys.schemas s
ON         s.schema_id = t.schema_id
INNER JOIN SYS.partitions p
ON         p.object_id = t.object_id
WHERE      p.index_id < 2
GROUP BY   s.name,
           t.name

执行速度明显快于常规

select count(*) from table

这是为什么?不应该优化数据库引擎以始终遵循最短路径吗?系统表视图解决方案的缺点是什么?

why this? shouldn't the db engine be optimized to follow the shortest path always? What's the drawback in the system tables view solution?

推荐答案

不保证存储在元数据中的值是正确的.DBCC UPDATEUSAGE 可用于在它发生时更正(更少可能从 SQL Server 2000 开始)

The value stored in the metadata is not guaranteed to be correct. DBCC UPDATEUSAGE can be used to correct this when it happens (less likely since SQL Server 2000)

它也不是事务一致的.您可以读取由未提交事务更新的计数值.

Also it is not transactionally consistent. You can read a count value updated by an uncommitted transaction.

这些值也可以手动更新.

And it is possible for these values to be manually updated as well.

CREATE TABLE dbo.YourTable(X INT)

UPDATE STATISTICS dbo.YourTable WITH ROWCOUNT = 1000000

SELECT     sum(p.rows) eRowCount
FROM       sys.partitions p
WHERE      P.object_id =object_id('dbo.YourTable') AND p.index_id < 2

相关文章