DELETE FROM tblFEStatsBrowsers WHERE BrowserID NOT IN (
SELECT DISTINCT BrowserID FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID IS NOT NULL
)
tblFEStatsBrowsers有553行.
tblFEStatsPaperHits有47.974.301行.
tblFEStatsBrowsers：
CREATE TABLE [dbo].[tblFEStatsBrowsers](
[BrowserID] [smallint] IDENTITY(1,1) NOT NULL,[Browser] [varchar](50) NOT NULL,[Name] [varchar](40) NOT NULL,[Version] [varchar](10) NOT NULL,CONSTRAINT [PK_tblFEStatsBrowsers] PRIMARY KEY CLUSTERED ([BrowserID] ASC)
)
tblFEStatsPaperHits：
CREATE TABLE [dbo].[tblFEStatsPaperHits](
[PaperID] [int] NOT NULL,[Created] [smalldatetime] NOT NULL,[IP] [binary](4) NULL,[PlatformID] [tinyint] NULL,[BrowserID] [smallint] NULL,[ReferrerID] [int] NULL,[UserLanguage] [char](2) NULL
)
tblFEStatsPaperHits上有一个聚簇索引,它不包含BrowserID.因此,执行内部查询将需要对tblFEStatsPaperHits进行全表扫描 – 这完全没问题.
目前,对tblFEStatsBrowsers中的每一行执行完整扫描,这意味着我已经对tblFEStatsPaperHits进行了553次全表扫描.
仅重写WHERE EXISTS不会改变计划：
DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
)
但是,正如Adam Machanic所建议的,添加HASH JOIN选项确实会产生最佳执行计划(只需对tblFEStatsPaperHits进行一次扫描)：
DELETE FROM tblFEStatsBrowsers WHERE NOT EXISTS (
SELECT * FROM tblFEStatsPaperHits WITH (NOLOCK) WHERE BrowserID = tblFEStatsBrowsers.BrowserID
) OPTION (HASH JOIN)
现在这不是一个如何解决这个问题的问题 – 我可以使用OPTION(HASH JOIN)或手动创建临时表.我更想知道为什么查询优化器会使用它当前的计划.
由于QO在BrowserID列上没有任何统计信息,我猜它假设最差 – 5000万个不同的值,因此需要相当大的内存/ tempdb工作表.因此,最安全的方法是对tblFEStatsBrowsers中的每一行执行扫描.两个表中的BrowserID列之间没有外键关系,因此QO无法从tblFEStatsBrowsers中扣除任何信息.
这听起来很简单,原因是什么？
更新1
给出一些统计数据：
选项(HASH JOIN)：
208.711次逻辑读取(12次扫描)
选项(LOOP JOIN,HASH GROUP)：
11.008.698逻辑读取(〜每个BrowserID扫描一次(339))
没有选择：
11.008.775逻辑读取(〜每个BrowserID扫描(339))
更新2
非常好的答案,各位 – 谢谢！艰难挑选一个.虽然马丁是第一个,而雷木思提供了一个很好的解决方案,但我必须把它交给新西兰人,让他们了解细节:)
解决方法
“I’m more wondering why the query optimizer would ever use the plan it currently does.”
换句话说,问题是为什么以下计划对于优化器来说看起来最便宜,与替代方案相比(其中有很多).
连接的内侧实际上是为BrowserID的每个相关值运行以下形式的查询：
DECLARE @BrowserID smallint;
SELECT
tfsph.BrowserID
FROM dbo.tblFEStatsPaperHits AS tfsph
WHERE
tfsph.BrowserID = @BrowserID
OPTION (MAXDOP 1);
请注意,估计的行数是185,220(而不是289,013),因为相等比较隐式地排除了NULL(除非ANSI_NULLS为OFF).上述计划的估计成本为206.8单位.
现在让我们添加一个TOP(1)子句：
DECLARE @BrowserID smallint;
SELECT TOP (1)
tfsph.BrowserID
FROM dbo.tblFEStatsPaperHits AS tfsph
WHERE
tfsph.BrowserID = @BrowserID
OPTION (MAXDOP 1);
估计成本现在为0.00452单位. Top物理运算符的添加在Top运算符中设置了一行row goal.那么问题就变成了如何为聚集索引扫描推导出“行目标”;也就是说,在一行与BrowserID谓词匹配之前,扫描期望处理多少行？
可用的统计信息显示166个不同的BrowserID值(1 / [All Density] = 1 / 0.006024096 = 166).成本计算假设不同的值在物理行上均匀分布,因此聚簇索引扫描的行目标设置为166.302(因为收集了采样统计信息,因此表基数的变化).
扫描预期的166行的估计成本不是很大(甚至执行339次,每次更改BrowserID一次) – 聚集索引扫描显示估计成本为1.3219单位,显示行目标的缩放效果. I / O和cpu的未缩放运算符成本分别显示为153.931和52.8698：
实际上,从索引扫描的前166行(无论它们碰巧返回的顺序)都不太可能包含每个可能的BrowserID值.尽管如此,DELETE计划的总成本为1.40921,并由优化程序选择. Bart Duncan在最近的一篇名为Row Goals Gone Rogue的文章中展示了这种类型的另一个例子.
值得注意的是,执行计划中的Top运算符与Anti Semi Join没有关联(特别是’短路’Martin提及).我们可以通过首先禁用名为GbAggToConstScanOrTop的探索规则来开始查看Top的来源：
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
SELECT DISTINCT BrowserID
FROM tblFEStatsPaperHits WITH (NOLOCK)
WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1,LOOP JOIN,RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');
该计划的估计成本为364.912,并显示Top替换了Group By Aggregate(按相关列BrowserID分组).聚合不是由于查询文本中的冗余DISTINCT：它是一个可以由两个探索规则LASJNtoLASJNonDist和LASJOnLclDist引入的优化.禁用这两个也会产生这个计划：
DBCC RULEOFF ('LASJNtoLASJNonDist');
DBCC RULEOFF ('LASJOnLclDist');
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
SELECT DISTINCT BrowserID
FROM tblFEStatsPaperHits WITH (NOLOCK)
WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1,RECOMPILE);
GO
DBCC RULEON ('LASJNtoLASJNonDist');
DBCC RULEON ('LASJOnLclDist');
DBCC RULEON ('GbAggToConstScanOrTop');
该计划的估计成本为40729.3单位.
如果没有从Group By到Top的转换,优化器“自然地”在反半连接之前选择具有BrowserID聚合的散列连接计划：
DBCC RULEOFF ('GbAggToConstScanOrTop');
GO
DELETE FROM tblFEStatsBrowsers
WHERE BrowserID NOT IN
(
SELECT DISTINCT BrowserID
FROM tblFEStatsPaperHits WITH (NOLOCK)
WHERE BrowserID IS NOT NULL
) OPTION (MAXDOP 1,RECOMPILE);
GO
DBCC RULEON ('GbAggToConstScanOrTop');
没有MAXDOP 1限制,并行计划：
另一种“修复”原始查询的方法是在执行计划报告的BrowserID上创建缺失索引.当内侧被索引时,嵌套循环最有效.估计半连接的基数在最好的时候是具有挑战性的.没有正确的索引(大表甚至没有唯一的密钥！)根本没有帮助.
保罗
