从 ADO.NET 将结构化数据传递到 SQL Server 存储过程的方法的实验性性能比较
本文对从 ADO.NET 将结构化数据传递到 SQL Server 存储过程参数的不同方法的性能进行了实验性比较。
引言
本文对从 ADO.NET 将结构化数据传递到 SQL Server 存储过程的不同方法的性能进行了实验性比较。
背景
程序员通常希望将一些结构化数据从 ADO.NET 传递到存储过程。例如，如果客户端应用程序需要更新数千名学生的分数，最好以某种结构化的方式捆绑信息，并将其传递给 SQL Server 存储过程参数，让存储过程以批处理方式处理信息。与调用存储过程数千次相比，这种方法至少有两个明显的优点：
- 减少网络流量，从而提高性能。
- 通过在存储过程中一起处理信息，更容易在存储过程中实现事务，以确保所有更新要么成功，要么失败。
尽管可以使用 ADO.NET 实现事务，但在逐条记录将信息传递给 SQL Server 时，一些数据库对象可能会被长时间运行的事务锁定，其他用户对这些对象的访问也会被阻塞。通过捆绑记录并以结构化方式将其发送到存储过程，存储过程可以以最适合数据库更新的形式预处理数据，从而减少锁定数据库对象所需的时间。
本文的主题已经被程序员们讨论了很久。CodeProject 上的一篇文章：将数组或 DataTable 传递到存储过程对此进行了很好的介绍。将信息以数组形式传递与多次调用存储过程之间的性能比较明显倾向于批处理方法。
随着 SQL Server 2005 中 XML 数据类型的引入以及 SQL Server 2008 中用户定义 `Table` 数据类型的引入，我们有了更多的选项来将结构化数据传递到 SQL Server。一般来说，我们至少有三种方法：
- 在过去，我们通过使用特殊字符分隔信息片段，将结构化数据作为
Text或VARCAHR传递给存储过程。 - 以 XML 格式传递信息。
- 将信息作为表值参数传递。
毫无疑问，以上三种方法都比逐条记录传递信息具有显著的性能优势，但这些方法本身之间的性能比较如何？本文旨在通过实验回答这个问题，结果显示出惊人的性能差异。
为了使比较具有实际意义，比较所测量的时间包括以下内容：
- 在客户端程序中，将结构化数据准备成适合传递给存储过程的形式所需的时间。
- 将数据作为存储过程参数上传到 SQL Server 所花费的时间。
- SQL Server 存储过程处理数据以使其易于被存储过程使用所花费的时间。在此实验中，它是将数据处理成临时表或某种等效数据类型所需的时间。
在本实验中，存储过程将以完全相同的方式将上传的数据返回给客户端程序。为了在进行实验时获得更愉悦的用户体验，此时间也包含在测量中。由于所有存储过程都返回相同的数据，因此花费的时间应该相似，并且不应改变实验结果。
本文包含完整的 C# 代码和 SQL 脚本来设置测试。有兴趣的读者可以下载代码并设置环境自行重复测试。如果您不熟悉如何将结构化数据传递到 SQL Server 存储过程，本文随附的代码也可以作为很好的参考。客户端 C# 代码是使用 Visual Studio 2008 中的 .NET 3.5 编写的，SQL Server 版本是 2008。
要传递给 SQL Server 存储过程的数据
为了进行比较，我们需要一些测试数据来传递给 SQL Server 存储过程。我根据两个标准选择测试数据：
- 所有三种方法都将使用完全相同的数据集。
- 数据量需要足够大，以便性能差异可见。
创建了一个单例类来存储要传递给存储过程的数据，这样在实验过程中它不会改变。
using System;
using System.Data;
namespace SQLSPPerformanceComparison
{
class DataSourceToUpload : DataTable
{
private static DataSourceToUpload _thisInstance = null;
private DataSourceToUpload() : base()
{
int NoOfRowsToSend =
System.Convert.ToInt32(
System.Configuration.ConfigurationSettings.AppSettings[
"NoOfRowsOfDataInTheTest"]);
Columns.Add("ID", System.Type.GetType("System.Int32"));
Columns.Add("Name", System.Type.GetType("System.String"));
Columns.Add("Score", System.Type.GetType("System.Int32"));
Random rd = new Random();
for (int Idex = 1; Idex <= NoOfRowsToSend; Idex++)
{
DataRow row = NewRow();
Rows.Add(row);
row["ID"] = Idex;
row["Name"] = "Student Name No. " + Idex.ToString();
row["Score"] = 60 + rd.NextDouble() * 40;
}
}
public static DataSourceToUpload GetInstance()
{
if (_thisInstance == null)
{
_thisInstance = new DataSourceToUpload();
}
return _thisInstance;
}
}
}
这个类表示一个 `DataTable`。`DataTable` 中填充了模拟学生 ID、姓名和分数的虚拟测试数据。这个类从“App.config”文件读取要填充到 `DataTable` 中的行数。在我的配置中，`DataTable` 有 100000 行。下图显示了 `DataTable` 的前 20 行。
ADO.NET 实用程序
本实验中的所有 SQL Server 连接都通过以下实用程序类进行
using System;
using System.Data;
using System.Data.SqlClient;
namespace SQLSPPerformanceComparison
{
class SQLServerUtility
{
public static DataTable GetADataTable(SqlCommand cmd)
{
// Create a connection object
// and obtain the connection string from App.config file
SqlConnection connection = new SqlConnection();
connection.ConnectionString =
System.Configuration.ConfigurationSettings.AppSettings[
"SQLServerConnectionString"];
cmd.Connection = connection;
cmd.CommandTimeout = 0;
connection.Open();
DataTable aTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
adapter.Fill(aTable);
return aTable;
}
}
}
公共静态方法“`GetADataTable`”从“App.config”文件获取连接字符串。它接受一个 `SqlCommand` 输入并调用 SQL Server 获取一个 `DataTable`。对于比较中的每种方法，我们将准备一个 `SqlCommand` 对象并使用此方法将数据发送到 SQL Server。
将数据作为由特殊字符分隔的文本传递
以下代码用于测试将数据作为 Text 传递给存储过程。每个数据项都由一个特殊字符分隔。这种与存储过程通信的方法在过去几年中一直被广泛使用。本文中分隔字符的选择与大多数介绍此技术的文章不同。为了避免在将数据内容传递给存储过程时需要转义，使用了两个 ASCII 码 0 和 1 作为分隔符。这两个 ASCII 码是不可打印的，因此它们不会与数据中的任何字符串或整数内容混淆。
private DataTable TestWithTextType()
{
char RowSeparator = System.Convert.ToChar(0);
char ItemSeparator = System.Convert.ToChar(1);
StringBuilder SB = new StringBuilder();
foreach (DataRow row in DataToUpload.Rows)
{
int ID = System.Convert.ToInt32(row["ID"]);
string Name = System.Convert.ToString(row["Name"]);
int Score = System.Convert.ToInt32(row["Score"]);
SB.Append(ID.ToString());
SB.Append(ItemSeparator);
SB.Append(Name);
SB.Append(ItemSeparator);
SB.Append(Score.ToString());
SB.Append(RowSeparator);
}
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UploadByTextType";
cmd.Parameters.Add("@DataInTextType",
SqlDbType.Text).Value = SB.ToString();
cmd.CommandType = CommandType.StoredProcedure;
DataTable aTable = SQLServerUtility.GetADataTable(cmd);
return aTable;
}
要上传的数据来自全局变量“`DataToUpload`”，该变量引用前面提到的“`DataSourceToUpload`”类的单个实例。接收数据的存储过程由以下脚本生成：
IF EXISTS (SELECT * FROM sys.objects WHERE
object_id = OBJECT_ID(N'[dbo].[UploadByTextType]') AND
type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UploadByTextType];
GO
CREATE PROCEDURE [dbo].[UploadByTextType]
@DataInTextType AS TEXT
AS
BEGIN
DECLARE @T AS TABLE([ID] INT, [Name] VARCHAR(250), [Score] INT)
DECLARE @RowSeparator AS INT; DECLARE @ItemSeparator AS INT;
DECLARE @P AS INT
DECLARE @DataLength AS INT
DECLARE @Item AS INT
DECLARE @C AS VARCHAR(1)
DECLARE @ID AS VARCHAR(20)
DECLARE @Name AS VARCHAR(250)
DECLARE @Score AS VARCHAR(20)
SET @RowSeparator = 0; SET @ItemSeparator = 1
SET @P = 1; SET @Item = 1; SET @ID = ''; SET @Name = ''; SET @Score = ''
SET @DataLength = DATALENGTH(ISNULL(@DataInTextType, ''));
WHILE @P <= @DataLength
BEGIN
SET @C = SUBSTRING(@DataInTextType, @P, 1)
IF ASCII(@C) = @RowSeparator BEGIN
INSERT INTO @T VALUES(CAST(@ID AS INT), @Name, CAST(@Score AS INT));
SET @Item = 1; SET @ID = ''; SET @Name = ''; SET @Score = '';
END ELSE
BEGIN
IF @Item = 1
BEGIN IF ASCII(@C) = @ItemSeparator BEGIN SET @Item = @Item + 1 END
ELSE BEGIN SET @ID = @ID + @C END END
ELSE IF @Item = 2
BEGIN IF ASCII(@C) = @ItemSeparator BEGIN SET @Item = @Item + 1 END
ELSE BEGIN SET @Name = @Name + @C END END
ELSE IF @Item = 3
BEGIN IF ASCII(@C) = @ItemSeparator BEGIN SET @Item = @Item + 1 END
ELSE BEGIN SET @Score = @Score + @C END END
END
SET @P = @P + 1
END;
SELECT * FROM @T
END;
GO
此存储过程解析来自 ADO.NET 客户端的数据并将其恢复到临时表中，然后将其返回给调用 ADO.NET 客户端。
以 XML 格式传递数据
随着 SQL Server 2005 中 `XML` 类型的引入，我们可以将结构化数据作为 XML 发送到存储过程。以下是客户端的 C# 代码
private DataTable TestWithXMLType()
{
System.Text.UTF8Encoding AEncoder = new UTF8Encoding();
System.IO.MemoryStream ms = new System.IO.MemoryStream();
System.Xml.XmlTextWriter tw =
new System.Xml.XmlTextWriter(ms, new System.Text.ASCIIEncoding());
tw.WriteStartDocument();
tw.WriteStartElement("DATAS");
foreach (DataRow row in DataToUpload.Rows)
{
int ID = System.Convert.ToInt32(row["ID"]);
string Name = System.Convert.ToString(row["Name"]);
int Score = System.Convert.ToInt32(row["Score"]);
tw.WriteStartElement("DATA");
tw.WriteElementString("ID", ID.ToString());
tw.WriteElementString("Name", Name);
tw.WriteElementString("Score", Score.ToString());
tw.WriteEndElement();
}
tw.WriteFullEndElement();
tw.WriteEndDocument();
tw.Flush();
tw.Close();
string strXML = AEncoder.GetString(ms.ToArray());
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UploadByXMLType";
cmd.Parameters.Add("@DataInXMLType", SqlDbType.Text).Value = strXML;
cmd.CommandType = CommandType.StoredProcedure;
DataTable aTable = SQLServerUtility.GetADataTable(cmd);
return aTable;
}
存储过程由以下脚本生成：
IF EXISTS (SELECT * FROM sys.objects WHERE
object_id = OBJECT_ID(N'[dbo].[UploadByXMLType]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UploadByXMLType];
GO
CREATE PROCEDURE [dbo].[UploadByXMLType]
@DataInXMLType AS TEXT
AS
BEGIN
DECLARE @I int
DECLARE @XML AS XML
SET @XML = CAST(@DataInXMLType AS XML)
EXEC sp_xml_preparedocument @I OUTPUT, @XML
SELECT *
FROM OPENXML (@I, '/DATAS/DATA', 1) WITH
([ID] INT 'ID', [Name] VARCHAR(250) 'Name', [Score] INT 'Score')
EXEC sp_xml_removedocument @I
END;
GO
将数据作为表值参数传递
随着 SQL Server 2008 中表值参数的引入，我们可以将 ADO.NET `DataTable` 作为存储过程参数传递到 SQL Server。以下是客户端 .NET 代码。尽管保存到单例类“`DataSourceToUpload`”中的数据本身就是 `DataTable`，但以下代码会重新生成一个 `DataTable`。在实际应用程序中，要发送的数据可能并不总是 `DataTable` 格式。计算生成 `DataTable` 所花费的时间可以使比较更具实际意义。
private DataTable TestWithSQLTableType()
{
DataTable TableToUpload = new DataTable();
TableToUpload.Columns.Add("ID",
System.Type.GetType("System.Int32"));
TableToUpload.Columns.Add("Name",
System.Type.GetType("System.String"));
TableToUpload.Columns.Add("Score",
System.Type.GetType("System.Int32"));
foreach (DataRow row in DataToUpload.Rows)
{
int ID = System.Convert.ToInt32(row["ID"]);
string Name = System.Convert.ToString(row["Name"]);
int Score = System.Convert.ToInt32(row["Score"]);
DataRow aNewRow = TableToUpload.NewRow();
aNewRow["ID"] = ID;
aNewRow["Name"] = Name;
aNewRow["Score"] = Score;
TableToUpload.Rows.Add(aNewRow);
}
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "UploadByTableType";
cmd.Parameters.AddWithValue("@DataInTableType", TableToUpload);
cmd.CommandType = CommandType.StoredProcedure;
DataTable aTable = SQLServerUtility.GetADataTable(cmd);
return aTable;
}
在 SQL Server 端，我们首先需要创建一个用户定义的 `Table` 类型，并将其用作存储过程参数类型。
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[UploadByTableType]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[UploadByTableType];
GO
IF EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss
ON st.schema_id = ss.schema_id
WHERE st.name = N'TStudentScore' AND ss.name = N'dbo')
DROP TYPE [dbo].[TStudentScore];
GO
CREATE TYPE [dbo].[TStudentScore] AS TABLE(
[ID] [int] NULL,
[Name] [varchar](250) NULL,
[Score] [int] NULL
);
GO
CREATE PROCEDURE dbo.UploadByTableType
@DataInTableType AS TStudentScore READONLY
AS
BEGIN
SELECT * FROM @DataInTableType
END;
GO
运行测试应用程序
要运行测试应用程序，您需要一个 SQL Server 2008 实例，并且需要足够的权限来运行上述 SQL 脚本以生成存储过程和用户定义的 `Table` 类型。
您还需要检查应用程序“App.config”文件中的内容。
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="SQLServerConnectionString"
value="Data Source=localhost;Initial Catalog=Experiment;Integrated Security=SSPI;"/>
<add key="NoOfRowsOfDataInTheTest"
value="100000"/>
</appSettings>
</configuration>
需要配置两个值。一个是连接 SQL Server 的连接字符串。您需要确保连接字符串具有足够的凭据来访问存储过程和用户定义的 `Table` 类型。在我的实验中，连接字符串中的登录是服务器管理员，因此我拥有无限访问权限。另一个要配置的值是用于比较的测试数据的行数。我在实验中将此值设置为 100000。
如果您有 Visual Studio 2008，您可以下载源代码并编译运行。如果您没有 Visual Studio，本文附带了一个编译好的 EXE。您可以简单地更改应用程序的配置文件并运行此 EXE 程序。
对每种方法花费的时间平均 10 次，我得到以下结果：
| 方法 | 花费时间（秒） |
作为由特殊字符分隔的 Text 传递 |
33 |
作为 XML 传递 |
8 |
作为 DataTable 传递 |
0.89 |
差异非常显著。作为 XML 传递比作为由特殊字符分隔的 Text 传递快 4 倍以上，而作为 DataTable 传递比作为 XML 传递快近 10 倍。下图显示了以 DataTable 格式上传数据时的结果。
是什么造成了如此大的差异？
为了回答这个问题，我做了另一个实验。我通过发布以下 SQL 脚本更改了三个存储过程，以便存储过程不解析信息，而是立即返回。
ALTER PROCEDURE [dbo].[UploadByTextType]
@DataInTextType AS TEXT
AS
BEGIN
-- Without processing the data passed over, simply return a simple
-- Recordset
SELECT 'A' AS A
END;
GO
ALTER PROCEDURE dbo.UploadByTableType
@DataInTableType AS TStudentScore READONLY
AS
BEGIN
-- Without processing the data passed over, simply return a simple
-- Recordset
SELECT 'A' AS A
END;
GO
ALTER PROCEDURE [dbo].[UploadByXMLType]
@DataInXMLType AS TEXT
AS
BEGIN
-- Without processing the data passed over, simply return a simple
-- Recordset
SELECT 'A' AS A
END;
GO
重复实验，我得到以下结果：
| 方法 | 花费时间（秒） |
作为由特殊字符分隔的 Text 传递 |
0.2 |
作为 XML 传递 |
0.43 |
作为 DataTable 传递 |
0.57 |
这个结果并不令人惊讶。对于相同的信息量，XML 字符串的长度比文本字符串的长度长，并且 `DataTable` 是比 XML 字符串更复杂的对象。总结两次实验的结果，我们可以得出以下结论：
- 单独将信息发送到 SQL Server 所花费的时间相对较短，与存储过程将信息处理成易于使用的形式所花费的时间相比。在本文中，我们编写存储过程在必要时将信息转换为临时表或等效形式，这是实践中最常见的形式。
- 以易于存储过程使用的形式发送结构化数据，例如 XML，尤其是 `DataTable`，我们可以显著提高整体应用程序性能。
更多关于将数据作为表值参数传递的信息
通过将结构化数据作为 `DataTable` 传递给存储过程所带来的巨大性能优势得到了证明。但这并不是故事的结局。如果我们在 SQL Server 数据库中创建一个新的登录名，并为存储过程分配执行权限，然后将“App.config”更改为使用此登录名，当将 `DataTable` 发送到数据库时，应用程序会立即失败。这是因为我们创建了一个用户定义的 `Table` 类型，我们需要向该登录名授予此用户定义类型的执行权限。我不确定为什么 Microsoft 添加了这一级别的安全性。当我们希望高效工作时，这似乎是一种不必要的维护成本。
结论
本文比较了通过不同方法将结构数据传递到 SQL Server 存储过程所花费的时间。我们可以发现，如果希望将信息在存储过程中转换为易于使用的形式，性能差异非常显著。当数据量较小时，这种差异不应显著影响整体应用程序。当数据量变大时，选择合适的方法变得必要。
性能绝不是选择不同方法的唯一原因，但它绝对值得成为原因之一，选择权在您。
本实验的局限性
这个实验似乎已经回答了我的问题，我也做出了选择，但它仍然有局限性。
- 我的整个测试都是在与客户端程序位于同一台计算机上的开发 SQL Server 上进行的，这在生产环境中很少见。当客户端和服务器位于不同计算机上时，结果会怎样？
- 我所有的方法实现都是最有效的实现吗？
要回答这些问题，需要进一步的测试。如果您有兴趣进行更广泛的测试，我渴望了解您的发现，并感谢您的反馈。
历史
这是本文的第一个修订版。