我有两个相同数据集的版本,需要进行全连接以查找其中一个缺失的记录,两个版本都有一些缺失的记录。我已经想出了两种方法来做到这一点,但是两种方法都有缺点。我的数据集大小和过滤条件非常大。
解决方案1的缺点是使用CTE,这将分割过滤器并使代码更难阅读,我希望只有一个查询:
create table #temp (id int, vers nvarchar(1))
insert into #temp select 1,'a' union select 2,'a' union select 3,'a'
union select 1,'b' union select 2,'b' union select 100,'b'
;WITH vers_a as (SELECT * FROM #temp WHERE vers = 'a')
,vers_b as (SELECT * FROM #temp WHERE vers = 'b')
SELECT ta.id, tb.id, ta.vers, tb.vers
FROM vers_a ta
FULL JOIN vers_b tb on ta.id = tb.id
WHERE ta.id is null or tb.id is null
drop table #temp
解决方案2复制了过滤器,执行计划更大:
create table #temp (id int, vers nvarchar(1))
insert into #temp select 1,'a' union select 2,'a' union select 3,'a'
union select 1,'b' union select 2,'b' union select 100,'b'
SELECT ta.id, tb.id, ta.vers, tb.vers
FROM #temp ta
FULL JOIN #temp tb on ta.id = tb.id and ta.vers = 'a' and tb.vers = 'b'
WHERE (ta.id is null or tb.id is null) and (ta.vers = 'a' or tb.vers = 'b')
drop table #temp
所以我的问题是,是否可能像解决方案2一样,但没有双重条件定义,并且执行计划比解决方案1更小?
编辑:在单个查询中运行两个解决方案时,我发现解决方案2的成本为26%,而解决方案1的成本为45%,尽管它具有更小的执行计划。如果可能的话,我希望使用更快的解决方案(不一定要像我在问题中说的那样具有更小的执行计划),而无需代码重复。
编辑2:很抱歉误导了第一个编辑版本,我不擅长优化:)我在约1.5百万行数据集上进行了测试,结果表明解决方案1更快。要获取数据集,请使用以下内容:
create table #temp (id int, vers nvarchar(1))
insert into #temp select 1,'a' union select 2,'a' union select 3,'a'
union select 1,'b' union select 2,'b' union select 100,'b'
while (select count(*) from #temp) < 1000000
begin
insert into #temp select id+ABS(CHECKSUM(NewId()))%10000, vers from #temp
end
EXCEPT
,看看你期望得到的输出是什么? - NickyvV