使用Replace和Length check避免SQL Not IN

Question

我遇到了这样一种情况:我必须动态创建我的SQL字符串，并且在可能的情况下尝试使用参数和sp_executesql，这样我就可以重用查询计划。通过大量的在线阅读和亲身体验，我发现"NOT In“和"INNER/LEFT JOIN”在基表(最左边)很大(15M行，50列)时执行速度很慢，而且开销很大。我还读到，应该避免使用任何类型的函数，因为它会减慢查询速度，所以我想知道哪种类型的函数更糟糕？

我在过去使用过这个变通方法，尽管我不确定这是不是最好的做法，以避免在项目列表中使用"NOT IN“，例如，我正在传递一个包含3个字符串的列表，例如带有管道分隔符(仅在元素之间)的列表：

LEN(@param1) = LEN(REPLACE(@param1, [col], '')) 

而不是：

[col] NOT IN('ABD', 'RDF', 'TRM', 'HYP', 'UOE') 

...imagine字符串列表的长度为1到大约80个可能的值，并且此方法本身也不适合并行化。

在这个例子中，我可以使用"=“来表示NOT IN，我会使用传统的列表技术来表示In，或者!=，如果这样更快的话，尽管我对此表示怀疑。这比使用NOT IN更快吗？

作为第三种可能的替代方案，如果我知道所有其他可能性( IN可能性，可能是列表长度的80-95倍)并传递这些可能性，结果会怎样；这将在应用程序的业务层完成，以减轻SQL Server的工作负载。对于查询计划的重用来说，这不是一个非常好的可能性，但如果它将一个大的讨厌的查询减少了一两秒，那为什么不呢？

我也擅长创建SQL函数。既然上面是字符串操作，那么CLR函数是最好的吗？

有什么想法？

提前感谢所有的帮助/建议/等等。

Answer 1

正如Donald Knuth经常(错误地)引用的那样，“过早优化是万恶之源”。

所以，首先，你确定如果你以最清晰和简单的方式(写和读)写你的代码，它执行的很慢吗？如果没有，在开始使用任何“聪明的”优化技巧之前，请检查它。

如果代码很慢，粗略地检查一下查询计划。大多数情况下，查询执行的时间比查询编译的时间要长得多，因此通常不必担心查询计划的重用。因此，构建最优的索引和/或表结构通常会比调整查询的构建方式提供更好的结果。

例如，我非常怀疑使用LEN和REPLACE的查询是否比没有使用IN的查询具有更好的性能-在这两种情况下，都会扫描和检查所有行是否匹配。对于足够长的列表，MSSQL优化器将自动创建一个临时表来优化相等比较。

更重要的是，这样的技巧往往会引入bug:比方说，如果col = 'AB‘，那么您的示例将不能正确工作。

IN查询通常比NOT IN更快，因为for IN查询只需检查部分行即可。该方法的效率取决于您是否能够足够快地获得IN的正确列表。

说到向服务器传递一个可变长度的列表，这里和其他地方都有很多讨论。通常，您可以选择：

• 表值参数(仅限MSSQL 2008+ )、
• 动态构建的SQL (容易出错和/或不安全)、
• 临时表(适用于长列表，编写和执行短列表的开销可能太大)、
• 分隔字符串(适用于行为良好的值的简短列表-如少数整数)、

<代码>H19 XML参数(有些复杂，但工作良好-如果您使用良好的XML库，并且不‘手动’构造复杂的XML文本)。<代码>H210<代码>F211

下面是一个article，它很好地概述了这些技术以及其他一些技术。

Answer 2

我发现"NOT IN“和"INNER/LEFT JOIN”在基表(最左边)较大时执行速度较慢且开销较大

如果您对表进行了正确的索引，那么它应该不会很慢。如果您有一个依赖子查询，则可能会使查询变慢。也就是说，必须为表中的每一行重新计算查询，因为子查询引用了外部查询中的值。

我也读到过，应该避免使用任何类型的函数，因为它会减慢查询速度

那得看情况。SELECT function(x) FROM ...可能不会对性能产生很大的影响。问题是当您在查询中的其他位置使用列的函数时，例如连接条件、WHERE子句或ORDER BY，因为这可能意味着无法使用索引。不过，常量值的函数不是问题。

关于您的查询，我会先尝试使用[col] NOT IN ('ABD', 'RDF', 'TRM', 'HYP', 'UOE')。如果速度很慢，请确保已对表进行了适当的索引。

Answer 3

首先，由于您只过滤掉一小部分记录，所以col上的索引很可能根本没有被使用，因此SARG能力没有实际意义。

所以剩下的就是查询计划重用。

• 如果您使用的是SQL Server2008，请将@param1替换为表值参数，并让您的应用程序传递该参数而不是分隔列表。这完全解决了您的问题。
• 如果您使用的是SQL Server2005，我认为这无关紧要。您可以拆分分隔的列表并对表使用NOT IN/NOT EXISTS，但是如果在col上得不到索引查找又有什么意义呢？

有人能说到最后一点吗？将列表拆分到一个表变量，然后反连接它会节省足够的CPU周期来抵消设置成本吗？

编辑，这是SQL Server2005使用XML的第三种方法，灵感来自OMG的链接：

DECLARE @not_in_xml XML
SET @not_in_xml = N'<values><value>ABD</value><value>RDF</value></values>'

SELECT * FROM Table1 
WHERE @not_in_xml.exist('/values/value[text()=sql:column("col")]') = 0

我不知道与分隔列表或TVP相比，它的性能有多好。