T-SQL：可选表连接？

Question

假设我有一个存储过程，当参数 @ComplicatedSearch = 1 时，会执行复杂的逻辑。 当它设置为1时，我将填充变量表 @ValidIds，其中包含此过程可以返回的有效行。 当它为0时，逻辑被绕过，我们不需要过滤要返回的行。

因此，在那个逻辑之后，我最终得到这样一条语句：

SELECT
    m.*
    ,a.*
FROM
    MyTable m
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId
WHERE
   (@ComplicatedSearch = 0 OR EXISTS(SELECT * FROM @ValidIds WHERE Id = m.Id))

这个方法可行，但是我认为在MyTable包含大量行的情况下，将MyTable与@ValidIds连接起来比使用EXISTS()更有效率。

是否有一种方法可以做到像下面这样而不写多个查询？（实际查询非常大，因此具有和没有连接的多个版本并不理想）

SELECT
    m.*
    ,a.*
FROM
    MyTable m
    ONLY DO THIS IF ComplicatedSearch = 1 PLEASE: INNER JOIN @ValidIds v
       ON m.Id = v.Id
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId

- John

---

你认为JOIN比EXISTS更快是错误的。在SQL Server中，EXISTS被实现为半连接并且非常高效，并且可以使用任何可用的连接类型。 - Martin Smith

@MartinSmith：我认为我想要避免的是在MyTable上进行完整的扫描；我希望通过连接来更有效地获取行。 - John

不确定在没有在这端设置测试的情况下，您将获得哪种计划。您可以尝试添加 OPTION (RECOMPILE) 以便考虑到 @ComplicatedSearch 的值和表变量基数。还要确保使用 Id primary key 声明表变量以在该列上创建索引。 - Martin Smith

Answer 1

另一个选项：

SELECT
   m.*     
  ,a.*
FROM MyTable m    
 INNER JOIN @ValidIds v
  ON m.Id = case
              when @ComplicatedSearch = 1 then v.Id  --  Filter rows
              else m.Id  --  Select all rows
            end
 INNER JOIN AdditionalInfoTable a    
  ON m.Id = a.MyTableId

您需要进行性能测试以确定其是否足够高效。一些快速测试显示（在我的数据上），无论第一个调用是复杂还是不复杂，都会生成相同的查询计划。

"分叉"方法（独立过程）应该是最有效的。然而，将相同的代码在两个不同的地方进行微小修改可能会导致主要支持问题，特别是当您必须对所有“实例”进行后续更改时。如果数据的总体大小（例如总体性能）不太大，则“一般适用于大多数情况”的方法可能最为有效。

- Philip Kelley

---

完美！唯一需要注意的是，当@ComplicatedSearch为0时，我需要在该表中插入一个虚拟行，以防止内部连接将所有内容清除。 - John

糟糕！我错过了！（在测试生成的查询计划时，我只是硬编码了一些值。） - Philip Kelley

不确定为什么这被接受，因为它似乎不起作用。在加入回同一张表的情况下，SQL服务器仍然必须包括@validIds的行，因为该表已经被加入了。结果是行数大幅增加（查询变得更昂贵、更慢），远超预期。 - user2719430

Answer 2

如果你希望提高效率，你应该注意到存储过程会在第一次运行时计算查询计划并缓存它，然后在之后的查询中重复使用。在这种情况下，它将根据 @ComplicatedSearch 的第一个值来选择使用 @ValidIds。

因此，我建议将存储过程编写为：

if @ComplicatedSearch = 1
   exec simple_search
else
   exec complex_search

其中，simple_search包含您的第一个查询，而complex_search还加入了@ValidIds。

是的，您会得到两次查询，但为了解决这个问题，我会创建一个视图。

create view helper as
begin
SELECT
    m.*
    ,a.*
FROM
    MyTable m
    INNER JOIN AdditionalInfoTable a
       ON m.Id = a.MyTableId
end

然后从该视图中进行简单选择，并复杂地加入到@ValidIds中

- mmmmmm