通过使用替换和长度检查来避免SQL Not IN

Question

我遇到一种情况，必须动态创建SQL字符串，并且在可能的情况下尝试使用参数和sp_executesql，以便可以重用查询计划。通过大量的在线阅读和个人经验阅读，我发现“
NOT IN”和“ INNER / LEFTJOIN”的执行速度较慢，并且在基表（最左侧）很大（150万行，其中有50列）时，价格昂贵）。我还读到应该避免使用任何类型的函数，因为它会减慢查询速度，所以我想知道哪个更糟？

尽管我不确定这是最好的做法，但我过去曾使用过这种解决方法，例如当我传入3个字符串的列表时，避免在项目列表中使用“ NOT
IN”例如，带有管道定界符（仅在元素之间）：

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

代替：

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

…想象一下，字符串列表的长度为1到大约80个可能的值，而且这种方法也无法使其自我参数化。

在此示例中，我可以对NOT NOT使用“ =”，对我的IN可以使用传统的列表技术，如果我怀疑，可以使用！=来加快速度。这比使用NOT IN快吗？

作为第三个可能的选择，如果我知道所有其他可能性（IN可能性，列表可能会长80-95倍），然后通过这些可能性，那该怎么办？这将在应用程序的业务层中完成，以减轻SQL
Server的工作量。对于查询计划重用而言，这不是一个很好的可能性，但是如果它使大型讨厌的查询节省了一两秒的时间，那为什么不行呢。

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

有什么想法吗？

在此先感谢您提供的所有帮助/建议/等。

Answer 1

正如Donald Knuth经常（错误）引用的那样，“过早的优化是万恶之源”。
因此，首先，您确定如果以最清晰，最简单的方式（编写和读取）编写代码时，它的执行速度会很慢吗？如果没有，请检查它，然后再开始使用任何“聪明”的优化技巧。

如果代码很慢，请彻底检查查询计划。在大多数情况下，查询执行所花的时间比查询编译要长得多，因此通常不必担心查询计划的重用。因此，建立最佳索引和/或表结构通常比调整查询的构建方式要好得多。
。

例如，我严重怀疑使用LEN和REPLACE进行查询的性能要优于NOT IN-
在任何一种情况下，所有行都将被扫描并检查是否匹配。对于足够长的列表，MSSQL优化器将自动创建一个临时表以优化相等性比较。
更有什者，类似这样的技巧往往会引入错误：例如，如果[col] =’AB’，您的示例将无法正常工作。

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

说到将可变长度列表传递到服务器，这里有关于SO和其他地方的许多讨论。通常，您可以选择：

• 表值参数（仅适用于MSSQL 2008+），
• 动态构造的SQL（容易出错和/或不安全），
• 临时表（适用于长列表，较短的列表可能在写和执行时间上有太多开销），
• 分隔的字符串（适合简短的“行为良好”的值列表，例如少数整数），
• XML参数（有点复杂，但是效果很好-如果您使用好的XML库并且不“手工”构造复杂的XML文本）。

这是一篇很好地概述了这些技术的文章，还有其他一些文章。