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

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

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

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

代替：

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

...想象一下字符串列表的长度为 1 到大约 80 个可能的值，并且此方法也不适合参数化。

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

作为可能的第三种选择，如果我知道所有其他可能性（IN 可能性，列表可能长 80-95 倍）并通过它们，会怎样？这将在应用程序的业务层中完成，以减轻 SQL Server 的工作负载。对于查询计划重用来说，这不是一个很好的可能性，但如果它可以让一个大的令人讨厌的查询减少一两秒，那为什么不呢？

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

想法？

预先感谢您提供的任何和所有帮助/建议/等。

正如唐纳德·高德纳 (Donald Knuth) 经常被（错误地）引用的那样，“过早的优化是万恶之源”。
因此，首先，您确定如果您以最清晰、最简单的方式（写入和读取）编写代码，它的执行速度会很慢吗？如果没有，请在开始使用任何“聪明”的优化技巧之前检查一下。

如果代码速度很慢，请彻底检查查询计划。大多数时候，查询执行的时间比查询编译的时间长得多，因此通常您不必担心查询计划的重用。因此，构建最佳索引和/或表结构通常会比调整查询的构建方式提供更好的结果。

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

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

说到将可变长度列表传递到服务器，这里和其他地方有很多讨论。一般来说，您的选择是：

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

这是一个article http://www.sommarskog.se/arrays-in-sql-2005.html对这些技术和其他一些技术有很好的概述。