代码之家 › 专栏 › 技术社区 › Ashish Gupta Shiva

Sql server 2005:“如果存在”和“计数(*)>0是否相同”的IO统计信息?

sql-server-2005

Ashish Gupta Shiva · 技术社区 · 14 年前

编辑

对于EXISTS和COUNT(*)>0,在Id列上生成With PK

Table 'TableWithHugeData'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

IF OBJECT_ID('TableWithHugeData') IS NOT NULL
BEGIN
DROP TABLE TableWithHugeData
END

CREATE TABLE TableWithHugeData
(
ID UNIQUEIDENTIFIER,
Name varchar(max)
)


DECLARE @Counter INT
SELECT @Counter = 0
WHILE (@Counter < 50000)
BEGIN
INSERT INTO TableWithHugeData
VALUES (NewId(),'Ashish ' + Convert(varchar(5000),@Counter))
SELECT @Counter = @Counter +1
END

存在

DBCC FreeProcCache
DBCC DROPCLEANBUFFERS
SET  STATISTICS IO ON
DECLARE @Id UNIQUEIDENTIFIER
SELECT @Id = '28BD1F4C-7D89-4731-9D2C-21ECB20500F8'
IF EXISTS (SELECT * FROM TableWithHugeData WHERE Id = @Id)
BEGIN
SELECT CONVERT(BIT, 1)
END
ELSE
BEGIN
SELECT CONVERT(BIT, 0)
END

存在的IO统计信息:-

表“TableWithHugeData”。扫描计数1,逻辑读取6,物理读取1,预读读取270,lob逻辑读取0,lob物理读取0,lob预读读取0。

计数(*)>0

DBCC FreeProcCache
DBCC DROPCLEANBUFFERS
DECLARE @Id UNIQUEIDENTIFIER
SELECT @Id = '28BD1F4C-7D89-4731-9D2C-21ECB20500F8'
IF (SELECT COUNT(*) FROM TableWithHugeData WHERE Id = @Id)>0
BEGIN
SELECT CONVERT(BIT, 1)
END
ELSE
BEGIN
SELECT CONVERT(BIT, 0)
END

* )>0**:-

表“TableWithHugeData”。扫描计数1,逻辑读取6,物理读取1,预读读取270,lob逻辑读取0,lob物理读取0,lob预读读取0。

如您所见,EXISTS和COUNT(*)>0的IO统计信息相同。我认为EXISTS会更快,执行更少的读取。我是不是丢了什么东西?定义主键会有什么不同吗?

1 回复 | 直到 14 年前

Quassnoi 14 年前

如您所见,EXISTS和COUNT(*)>0的IO统计信息相同。我认为EXISTS会更快,执行更少的读取。我是不是丢了什么东西?

SQL Server 按块读取数据。

如果您要查找的记录恰好位于最后一个块中(或根本找不到),则 EXISTS 仍然需要扫描所有的区块。

定义主键会有什么不同吗?

对。

PRIMARY KEY , 存在 和 COUNT(*) 总是相同的(单索引搜索)。

存在 计数(*)

更新:

对不起,我刚才错了。

SQL服务器 优化 COUNT(*) > 0 到 存在

DBCC FreeProcCache
CHECKPOINT
DBCC DROPCLEANBUFFERS
SET  STATISTICS IO ON
DECLARE @id UNIQUEIDENTIFIER
SET @id = '7C65EBB1-1242-4084-8BB4-3CC92CA2BE51'
IF      (
        SELECT  COUNT(*)
        FROM    tablewithhugedata
        WHERE   id = @id
        ) > 0
BEGIN
        SELECT CONVERT(BIT, 1)
END
ELSE
BEGIN
        SELECT CONVERT(BIT, 0)
END

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'TableWithHugeData'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 270, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

还有一点不同的问题:

DBCC FreeProcCache
CHECKPOINT
DBCC DROPCLEANBUFFERS
SET  STATISTICS IO ON
DECLARE @id UNIQUEIDENTIFIER
SET @id = '7C65EBB1-1242-4084-8BB4-3CC92CA2BE51'
IF EXISTS
        (
        SELECT  1
        FROM    tablewithhugedata
        WHERE   id = @id
        )
BEGIN
        SELECT CONVERT(BIT, 1)
END
ELSE
BEGIN
        SELECT CONVERT(BIT, 0)
END

DBCC FreeProcCache
CHECKPOINT
DBCC DROPCLEANBUFFERS
SET  STATISTICS IO ON
DECLARE @id UNIQUEIDENTIFIER
SET @id = '7C65EBB1-1242-4084-8BB4-3CC92CA2BE51'
IF      (
        SELECT  COUNT(*)
        FROM    tablewithhugedata
        WHERE   id = @id
        ) BETWEEN 1 AND 2
BEGIN
        SELECT CONVERT(BIT, 1)
END
ELSE
BEGIN
        SELECT CONVERT(BIT, 0)
END

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Table 'TableWithHugeData'. Scan count 2, logical reads 266, physical reads 6, read-ahead reads 270, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

计数(*) 所以它读所有的方块( 266 逻辑读取与。 1 对于 EXISTS / COUNT(*) > 0 ).