如何在T-SQL中使用变量和多个常数优化条件

Question

我在t-sql中有一个简单的查询：

SELECT
  *
FROM
  Table t
WHERE
  t.Column IN ( 'Value1', 'Value2', 'Value3', ..., 'ValueN' )
;

当然，查询实际上与几个加入和子征服更为复杂，但这目前并不重要。

问题是：以下哪项在性能方面更快？

(1(

的原始条件

t.Column IN ( 'Value1', 'Value2', 'Value3', ..., 'ValueN' )

(2(使用一个名称值(可能是主键(的表值元素，该表被值'value1'，'value2'，...

填充

SELECT
  *
FROM
  Table t
WHERE
  t.Column in ( SELECT ve.Value FROM ValueEnumeration ve )
;

(3(使用用户定义的函数(UDF(，标量函数是精确的，称为isallowedvalue。

功能：

CREATE FUNCTION dbo.IsAllowedValue ( @ValueToCheck VARCHAR(20) ) RETURNS INT
AS
BEGIN
  IF @ValueToCheck = 'Value1'
     OR @ValueToCheck = 'Value2'
     OR @ValueToCheck = 'Value3'
     ...
     OR @ValueToCheck = 'ValueN'
  BEGIN
    RETURN 1;
  END;
  RETURN 0;
END
;

查询：

SELECT
  *
FROM
  Table t
WHERE
  dbo.IsAllowedValue(t.Column) = 1
;

好吧，我想第一个将是最快的解决方案。但是，我需要在存储过程中的许多地方进行类似的检查。一旦未来的原始枚举变化(这很可能发生 - 例如，必须添加新值(，您将必须转到代码中原始条件的所有出现并添加新的情况那里的价值。因此，我决定采用更可重复使用的解决方案。但是我不知道该选择哪一个。我有时需要以另一种方式进行测试(WHERE t.Column NOT IN (...)(。在我看来，在INNER JOIN(用于正检查(或LEFT OUTER JOIN(用于负检查(中使用表值Enumeration(用于负检查(，但由于我有大约大约，这将是痛苦的。代码中此类条件的50个位置，通常添加加入会大大改变SQL查询的外观以及执行计划，后者并不总是好的。

您有任何想法吗？

Answer 1

解决方案2只要您在调用查询之前存储允许的值。这不会影响您的性能(您将获得一次值，而不是表中的每个记录(，并且比解决方案1更可重复使用。

declare @AllowedValues table(val varchar(...))
insert into @AllowedValues 
SELECT ve.Value FROM ValueEnumeration ve

然后您可以在代码中使用它：

......
WHERE
  t.Column in ( SELECT val FROM @AllowedValues )

Answer 2

好吧，我最终决定为第三(通常不建议使用(解决方案(创建一个UDF(。它似乎是O.K.在性能方面。或者，至少它不比第二个解决方案慢("允许"值的表(。

一个功能，尽管通常认为是许多SQL查询的瓶颈，但提供了几个优点：

(i(它可以重新使用且易于调整(例如，如果将来必须添加一些新值(。

(ii(与枚举值表不同，每当您看到函数的ddl，函数定义时，您都可以查看当前正在使用的值(常数((第一个解决方案的优势，该值未重新确定虽然可用(。如果您使用了表，则必须执行选择以检查当前的值。

(iii(即使是语法上，写

也更容易

dbo.IsAllowedValue(t.Column) = 1

比

t.Column IN (SELECT Value FROM ValueEnumeration)

如果将来有任何不好的经历，我将提供有关该主题的更多评论。