SQL Server 中的 In-Clause 技术
4.86/5 (14投票s)
几种使用 SQL Server 和 C# 处理 IN 子句和存储过程的方法。
引言
本文提供了一些使用查询中的“in-clause”、ADO.NET、存储过程和 SQL Server 的技术。
背景
在多年的开发过程中，尝试不同的方法来解决同一个问题可能会很有趣。在这种情况下，就是寻找处理 SQL Server 和其他 RDBMS 中查询“in-clause”问题的最佳方法，其中子句的值数量未知。
典型场景是构建一组要查询的行 ID。场景可能是一个用户在列表框中选择几个值的屏幕，或者是一个网格中有一堆复选框。另一个场景可能是批处理过程，用于识别要处理的 ID 集合。ID 通常是唯一的，有时会在集合中出现多次。
那么，我们有多少种不同的方法可以设置这样的内容
select * from sometable where id in ( X number of ids )
让这个问题棘手的部分是，in-clause 中的每个值都必须是独立的。
Select * from sometable where id in (1, 400, 33, 333)
因此，使用一个变量来表示“1,400,33,333”是行不通的。
Declare @ids = '1,400,33,333'
select * from sometable where id in (@ids) -- boom
方法
好了，那么有什么方法可以解决这个问题呢：动态 SQL、参数化 SQL、使用分隔符字符串和临时表的存储过程、使用分隔符字符串到参数表的存储过程、使用分隔符字符串和变量表的存储过程、使用 XML 的存储过程以及使用值类型的存储过程。这应该足够了。我这里主要关注 SQL Server 2008。对于 SQL Server 和其他 RDBMS 也有其他技术。
在使用这些方法时，需要牢记几点。它们会消耗多少网络带宽？在客户端准备它们有多容易？它们会消耗多少内存？RDBMS 对此的处理效果如何？
为了使用这些方法，我们有一个简单的数据库，名为 In-Clause。其中有一个表 SomeString，它有一个 ID 字段和一个字符串字段。为了性能和唯一性，有一些索引。还有两个表用于参数表。该数据库包含一些存储过程、一个函数，甚至一个用户定义类型。对于代码，我尝试使项目保持简单，包含一个 Windows 项目和一个 MS Unit Test 项目。如果您要设置自己的测试环境，请确保在数据库文件 *temp*、*temp log*、*In-Clause* 和 *In-Clause log* 中设置足够的空间。我们希望有足够的空间来运行事物，避免因为文件需要增长而停滞（我最终将所有文件设置为 100MB）。
一些代码讨论
在看到一些结果之前，让我们先谈谈这些方法。有一个通用类 `TestClassDac`，它包含所有数据调用。Windows 窗体和单元测试会运行 `TestClassDac` 中的代码。
为了确保其正常工作，测试项目中的 `TestClassDacTest` 为每种方法都有一些测试。名称遵循方法，例如 `GetUsingDynamic_expect50`、`GetUsingDynamic_expect500` 和 `GetUsingDynamic_expect50000`。“50,000！”是的，这有很多 ID。它有效吗？这取决于。为了帮助找到最大输入大小，Windows 窗体中的“RunIt”按钮可以运行代码，以查找每种方法可以处理的最大 ID 数量。
动态 SQL
这是一种非常简单的方法。基本上，一个函数 `TestClassDac.GetUsingDynamic` 接收传入的 ID 列表来生成 SQL 语句。一个辅助函数构建 in-clause 部分。`DataReader` 用于执行语句并返回数据。使用一组熟悉的命令来处理结果，创建函数返回的对象集合。
参数化 SQL
什么是参数化 SQL？它与动态 SQL 非常相似。该方法在客户端构建 SQL 语句，并将字符串传递给 RDBMS 以执行查询。但是，值不是直接传递到 SQL 中，而是被替换为参数，参数的设置方式与处理存储过程时非常相似。预期 RDBMS 会将 SQL 更改为类似于临时存储过程的内容，以便重复使用。不同的 RDBMS 处理参数化 SQL 的方式不同。性能上的重要一步是认识到在过程缓存中存在类似的查询，因此可以使用缓存中的查询信息。代码包含与动态 SQL 类似的函数和测试。
分隔符字符串到临时表
此方法使用一个接受分隔符 ID 列表的存储过程。因此，.NET 代码使用 ID 列表创建一个分隔符 ID 列表，例如“1,4,6,33”，存储在一个字符串中。该字符串传递给存储过程。存储过程使用另一个过程来创建一个填充了值的临时表。查询不再使用 in-clause，而是可以使用 `exists` 语句。由于分隔符字符串的构建发生在几个地方，因此创建了一个辅助函数来生成字符串。
分隔符字符串到表变量
与“分隔符字符串到临时表”方法类似，此方法使用一个函数返回一个表变量，其中填充了字符串中的值。然后，表变量可以像临时表一样使用。
分隔符字符串到参数表
创建临时表和表变量会影响临时数据库，并可能导致比预期更多的重新编译，具体取决于编码方式。可以使用参数表来避免使用临时数据库。来自分隔符字符串的值将被插入到参数表中。为了区分列表，将使用公共标识符对值进行分组。有几种创建分组的方法，因此这里尝试了两种。一种方法使用 GUID/UniqueIdentifier 进行分组。另一种方法使用 bigint 进行分组。同样，这里的目标是尽量少地使用临时数据库，并且存储过程不会频繁重新编译。
XML
这个可能一开始看起来有点诡异。文档需要一些工作才能更容易使用。它几乎就像用户已经知道一样编写，而编写者却没见过 .NET 中生成的常规 XML。因此，这个示例可以为某些人节省几个小时的摸索时间。一个辅助函数从 ID 列表中构建 XML。XML 作为 XML 参数传递给存储过程。存储过程使用简单的 XQUERY，可以将 XML 用作表。这样使用 XML 是惊人的。
类型化值
新出现的特性是类型化值和结构化参数。它们似乎是为了加速 DataTables 而设计的，但我们也可以使用它们。在此示例中，一个辅助函数构建了一个结构化数据列表。一个简单的用户定义类型将结构化数据描述为存储过程使用的类型化值。集合一次性传递给存储过程。这几乎就像将类传递给存储过程，但又不是完全一样。在这种情况下，一旦在存储过程级别，就可以像对待表一样对待数据，但它实际上并不是一个表。
结果的批判：性能与可维护性
在工程环境中审查工具或技术时，重要的是要考虑其性能和可维护性。使用此处的技术时，性能因素包括首次运行时间、平均运行时间、容量和占用空间。可维护性的几个因素包括复杂性和可访问性。
那么，让我们看看这些技术的效果如何。
有单元测试，用于多次运行相同的方法，每次使用固定数量的随机输入，以及多次运行相同的方法，每次使用随机数量的随机输入。有一个 Windows 窗体应用程序，其中有几个按钮尝试查找每种方法支持的最大数量。使用 Windows 窗体来查看该方法如何通过增量 1000 来查找最大值，通常允许它们运行直到出现错误。固定数量的随机输入测试从 `SetTimesForSetRandom` 开始，运行 10000 次，每次使用 50 个介于 0 和 2500 之间的随机数。随机数量的随机输入测试从 `SetTimesForRandom` 开始。`SetTimesForRandom` 测试运行 10000 次，每次使用介于 2 和 200 之间的随机数量的输入，以及介于 0 和 2500 之间的随机数。我们尝试使用类似的技术来计时构建输入、执行查询和收集数据所需的时间。
动态 SQL
对于此场景，此技术性能“尚可”，基本上是传递 ID 以从一个表中检索一组行。SQL 语句的大小存在 RDBMS 限制，因此必须予以考虑。现在，SQL Server 的后期版本有趣之处在于，它会尝试对语句进行参数化。请查看此文章：http://www.sqlteam.com/article/introduction-to-parameterization-in-sql-server，甚至可以查看帮助文档。总之，SQL Server 2005 和 2008 会尝试在创建计划并运行查询之前，将固定值更改为参数。如果出现一个查询基本上会更改固定值，它将重用旧计划。现在，所有这些工作可能都会付出一些代价，但这个代价可能低于 SQL Server 从头开始计算计划的代价。在运行 SQL Profiler 时，可以看到 SQL Server 重用仅更改其值的动态 SQL 语句。要看到这一点，请启用缓存命中。SQL Server 的方法也有助于将过程缓存中的内存量降至最低。某些 RDBMS 或旧版本会将 SQL 语句放入过程缓存。因此，除非语句完全匹配，否则会在过程缓存中产生另一个语句。在这里的测试中，动态 SQL 语句达到了可传递 ID 数量的限制。其他 RDBMS 可能由于其处理 in-clause 的方式而达到不同的限制。此方法的一个问题是，如果 ID 数量发生变化，SQL Server 将不会重用相同的计划，并且过程缓存将开始填充。从可维护性角度来看，动态 SQL 位于代码中，因此它随代码一起提供。由于在代码中，安装程序在环境中少了一项安装要担心；没有存储过程。如果查询是用 ANSI 兼容 SQL 编写的，大多数 RDBMS 都会接受该查询，从而使代码可移植到 RDBMS。另一个有趣的观点是，对于不同的动态 SQL，每次调用都会使用最新统计信息进行编译。使用最新统计信息进行编译可能有利，也可能不利。
继续，在这个简单的测试环境中，动态 SQL 的表现如何？
| 固定数量的随机 | 93,271毫秒 |
| 随机数量的随机 | 232,745毫秒 |
| 最大输入 | 31,000....因错误而停止 |
可以看出，当动态查询具有与之前运行的查询相似的签名时，SQL Server 的表现良好。因此，SQL Server 的参数化似乎有效。当查询不相似时，其性能与反复运行查询的第一次一样。最大输入量是合理的，因为发送的查询大小至少为 62,000 个字符。实际上，消息是系统内存不足。
参数化 SQL
与动态 SQL 类似，客户端创建查询。现在，要注意的一点是，这里使用了一个计划。该计划将 SQL in-clause 分成几个 in-clauses。为什么？因为我们需要尝试让 RDBMS 反复看到相同的查询。为了使其看起来相同并重用过程缓存，参数的数量需要相同。为了最小化参数数量和查询签名数量，in-clause 可以被拆分并 OR 连接起来。困难的问题是如何设置计划。如果我们为计划中的输入数量设置为 2,100 个参数，那么这将是一个很大的查询，需要反复发送。缺点是，为了创建计划，我们必须“OR”连接 in-clauses 来查找结果。使用“OR”将 in-clauses 组合在一起可能会很慢。更困难的是，某些 RDBMS 只支持 in-clause 中的一定数量的值或参数，例如 1000 个项。从场景的角度考虑，如果计划是 10 个、15 个和 25 个。对于 10 个输入，只有一个 in-clause。对于 14 个输入，将有两个 in-clauses，其中最后输入的 11 个被设置为与最后一个输入相同的值。对于 20 个输入，将有两个 in-clauses，其中最后输入的 5 个被设置为与最后一个输入相同的值。此时，只有两个查询签名。20 个输入的查询将重用 14 个输入的查询计划（如果可用）。所以，如果您尝试计划方法，至少这是一个开始。
| 固定数量的随机 | 17,716毫秒 |
| 随机数量的随机 | 71,401毫秒 |
| 最大输入 | 2,100（根据文档） |
正如我们所见，参数化 SQL 的表现优于动态 SQL，但其支持的参数数量要少得多。
分隔符字符串到临时表
经过时间考验的经典技术。接受分隔符字符串作为参数，将值放入临时表，然后进行处理。此方法创建大量临时表。它使用存储过程。临时表可以在存储过程中重用。如果存储过程编写得当，它将不需要重新编译，因此可以重用查询计划。此方法的一个棘手之处在于写入临时表所需的时间。所以，如果您使用此技术，请确保设置一个快速的 tempdb。
| 固定数量的随机 | 115,582毫秒 |
| 随机数量的随机 | 211,766毫秒 |
| 最大输入 | 87,000....在此值时因超时错误而停止。 |
您的环境可能会产生不同的值。我们可以看到由于必须填充另一个表而导致的性能下降。但是该过程可以支持更多的输入。
分隔符字符串到表变量
这是临时表的一个变体，但使用了函数返回的表。表变量的某些行为与临时表略有不同。这是一篇讨论此变量最新版本的博客：SQL Server 存储引擎。
| 固定数量的随机 | 26,534毫秒 |
| 随机数量的随机 | 50,430毫秒 |
| 最大输入 | 88,000....在此值时因超时错误而停止。 |
结果表明，在此场景中，表变量的性能与临时表相比相当不错。然而，当大小达到某个点时，该变量就开始表现得像临时表一样。
一个 `Function` 有助于创建表变量
CREATE FUNCTION [dbo].[ufnDelimitedBigIntToTable]
(
@List varchar(max), @Delimiter varchar(10)
)
RETURNS @Ids TABLE
(Id bigint) AS
BEGIN
DECLARE @list1 VARCHAR(MAX), @Pos INT, @rList VARCHAR(MAX)
SET @List = LTRIM(RTRIM(@List)) + @Delimiter
SET @pos = CHARINDEX(@Delimiter, @List, 1)
WHILE @pos > 0
BEGIN
SET @list1 = LTRIM(RTRIM(LEFT(@List, @pos - 1)))
IF @list1 <> ''
INSERT INTO @Ids(Id) VALUES (CAST(@list1 AS bigint))
SET @List = SUBSTRING(@List, @pos+1, LEN(@List))
SET @pos = CHARINDEX(@Delimiter, @list, 1)
END
RETURN
END
创建后，表函数可以在查询中使用
CREATE PROCEDURE [dbo].[GetUsingDelimitedFunctionTable]
@Ids varchar(max)
AS
BEGIN
SET NOCOUNT ON
SELECT s.Id,s.SomeString
FROM SomeString s (NOLOCK)
WHERE EXISTS ( SELECT *
FROM ufnDelimitedBigIntToTable(@Ids,',') Ids
WHERE s.Id = Ids.id )
END
此代码会生成一个有趣的查询计划，但它运行良好。`EXISTS` 在逻辑上很有帮助，因为我们只关心值是否存在。一种想法是使用 `INNER JOIN`。然而，如果一个值在分隔符字符串中出现多次，`INNER JOIN` 将返回多个行。如果不需要多行，则需要在 `INNER JOIN` 的某个地方使用 `Distinct`。这完全取决于场景来帮助决定方法。在此情况下，我们试图模拟 in-clause。
分隔符字符串到参数表
与临时表方法非常相似，但写入永久表。永久表充当参数表。起初，此方法与临时表的方法相同，一次插入一行。第二次尝试时，使用了将输入分隔符字符串转换为表的函数。然后，将返回表中的值一次性插入到参数表中。为了将记录分组，插入的组会附加一个 GUID。这里，GUID 进入 JobId 字段。还有另一组测试使用 Bigint 作为 GUID。Bigint 方法从另一个表获取其值，以确保唯一性并了解其创建时间。性能有时比临时表慢，有时又比临时表快。尽管表变量的使用确实在一定程度上影响了 tempdb，但创建的临时表的数量要少得多。将数据保留在永久表中的一个好处是，数据可以被重用，例如用于分页或创建结果的不同视图。偶尔，应该清除参数表中的行，以免其过度增长。需要注意的一点是，保持表中有足够的数据，以便过程不会重新编译或使用过时的统计信息。要么是这样，要么是强制使用计划。
使用 GUID 的一些结果
| 固定数量的随机 | 137,808毫秒 |
| 随机数量的随机 | 203,800毫秒 |
| 最大输入 | 87,000....在此值时因超时错误而停止。 |
使用 BIGINT 的一些结果
| 固定数量的随机 | 74,307毫秒 |
| 随机数量的随机 | 134,114毫秒 |
| 最大输入 | 83,000....在此值时因超时错误而停止。 |
同样，您的数字将与这些不同。减小 JobId 列的大小似乎有所帮助。进一步减小到整数应该会有更大的帮助。如果减小到整数，请记住整数的最大值（20 亿），并且需要重置它的时间会早一些。
XML
此方法传递 XML 字符串。创建 XML 字符串的一种方法是使用 `XmlTextWriter`，另一种方法是使用 `StringBuilder`。如果您胆大，有一种方法是使用流。有几个测试方法用于比较它们的性能。在这里，我们选择了 `StringBuilder` 来制作 XML。由于 XML 易于创建，大多数知道如何使用它的 .NET 程序员，并且 `StringBuilder` 的工作速度比 `XmlTextWriter` 快，因此选择很简单。流方法效果很好，但会带来一个优势，即对于此测试，它可能“不保留字段级别”。在看到流方法之后，您可能会决定在许多情况下使用它而不是 `StringBuilder`，只需注意编码即可。构建的 XML 非常基础。使其在存储过程中正常工作需要一些摸索。大多数 .NET 编码人员不熟悉存储过程中的 XML，文档很粗糙（在我们看来），并且没有太多关于它的互联网社区讨论。这里有几篇文章：使用 SQL Server 2005 中的 XQuery 解析 XML 数据，SQL Server 2005 中的 XQuery 入门。XML 的一个巨大优势是它不是表。因为它不是表，所以没有锁定，它在内存中。有不同的方法来处理 XML。这里的代码基本上是将 XML 转换为表，以便于处理。因此，学习大量 XQUERY 的必要性不大，尽管可以尝试使用“exists”。
| 固定数量的随机 | 27,935毫秒 |
| 随机数量的随机 | 49,701毫秒 |
| 最大输入 | 150,000....在此值时手动停止 |
是的！性能提高了数量级。在内存中工作可能很棒。那么，我的分布式内存 RDBMS 在哪里？哦，还没出来。现在，在内存中处理数据最终可能会成为可伸缩性问题，但对于较小的数据集，它应该效果很好。
在此方法中，我们传递了类似这样的 XML
'<L><I>1</I><I>2</I><I>3</I><I>4</I><I>5</I><I>6</I> <I>7</I><I>8</I><I>99999</I><I>2</I><I>3</I><I>4</I></L>'
存储过程使用 XML 和
SELECT s.Id,s.SomeString
FROM SomeString s (NOLOCK)
--needs more tweeking
WHERE EXISTS (SELECT * FROM
( select T.c.value('.','bigint') as Id
from @Ids.nodes('/L/I') T(c)) Ids
where s.Id = Ids.id)
)
可能还有其他方法可以调整，但这很有效。也许以后，您可以尝试一些类似的方法
select @test.exist('/L/I[text()[1] = @somevariable] ')
类型化值
一个新工具。在客户端处理起来有点不同，但仍然相当容易。此方法的一个大优势是它将传入的数据存储在内存中，而不是表中。这是一个关于它们的文档的链接：类型化值参数。
| 固定数量的随机 | 28,194毫秒 |
| 随机数量的随机 | 37,443毫秒 |
| 最大输入 | 150,000....在此值时手动停止 |
类型化值方法的性能与 XML 方法相当。许多人会发现它们比 XML 更容易处理。此方法的一个问题是代码的可移植性；它不是 ANSI 标准。其他 RDBMS 及其 .NET 客户端可能有类似的方法，例如数组，它们可能值得考虑用于性能。由于类型化值驻留在内存中，因此在可伸缩性方面可能存在一些担忧。
使用以下方法创建类型化值很容易
CREATE TYPE [dbo].[IdsTableType] AS TABLE(
[Id] [bigint] NULL
)
用 C# 填充集合的代码很容易
public List<SqlDataRecord> ListToSqlDataRecord(List<long> items)
{
List<SqlDataRecord> records = new List<SqlDataRecord>();
SqlDataRecord recordProto;
//SqlDataRecord record;
SqlMetaData[] metas = new SqlMetaData[]{new SqlMetaData("Id",SqlDbType.BigInt)};
foreach (long item in items)
{
recordProto = new SqlDataRecord(metas);
recordProto.SetInt64(0, item);
records.Add(recordProto);
}
return records;
}
然后，只需要少量代码即可使用此过程并获取结果
const string sqlGetUsingTypeValue = "GetUsingTypeValue";
public List<TestClass> GetUsingSPTypeValue(List<long> ids)
{
List<TestClass> results = new List<TestClass>();
List<SqlDataRecord> idsDataRecords = ListToSqlDataRecord(ids);
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString =
ConfigurationManager.ConnectionStrings[DatabaseKey].ConnectionString;
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = sqlGetUsingTypeValue;
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter parm = cmd.Parameters.Add(parmIds, SqlDbType.Structured);
parm.TypeName ="dbo.IdsTableType";
parm.SqlValue = idsDataRecords ;
con.Open();
using (IDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
TestClass obj = new TestClass();
FillObject(dr, obj);
results.Add(obj);
}
}
}
return results;
}
棘手的部分是匹配类型名称与用户定义的表类型。
结论
这里的代码提供了一些关于如何处理 in-clause、SQL Server 和 .NET 的示例。XML 和类型化值方法在性能方面表现出色。所有方法都有优点和缺点。此处列出的时间是通用值，绝非官方。这里没有的一组信息是首次运行时间。使用的环境产生的数字过于混乱，无法显示可靠的数字。因此，请确保您进行测试以找出最适合您环境的方法。希望其中的一些代码可以帮助您改进开发中的决策。
当然，在完成这篇文稿后，我发现关于这个主题和其他 SQL Server 常见编程问题的不错的讨论，可以在Erland Sommarskog 的主页找到。
运行演示
从 *InClause.bak* 恢复 InClause 数据库的备份。更改 Windows 项目和测试项目中的配置文件中的连接字符串。通过按下表单上的“makedata”按钮，用行数据填充 SomeString。按钮旁边的输入框用于设置要创建的行数。然后，使用按钮和单元测试来尝试。