对于用户定义表类型,它实际上并不能提高性能,会觉得它看起来高效的原因,是因为在程序中使用这个种类型的参数的时候,可以把 DataTable 做为参数直接传递给存储过程(看起来非常简洁)
但实际上去Trace其行为,会发现其实它相当于把 DataTable 的值包装成了 insert 语句,而不是真正的把数据做为块传给存储过程
DBA这边的建议是
1.对于少量数据,我们建议拼成 xml ,存储过程里面做解析(附件有一个开发那边提供的测试,对于少量数据,不足1万条,xml解析的性能并不差).
2.对于大量数据,我们建立使用SqlBulkInsert 来处理.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
// 批量数据的源表,存放被用于批量处理的数据
DataTable tbSrouce = new DataTable("SourceData");
tbSrouce.Columns.Add("id", Type.GetType("System.Int32"));
tbSrouce.Columns.Add("name", Type.GetType("System.String"));
tbSrouce.Rows.Add(1, "aaa");
//批量数据导入临时表,并用于后续处理
using (SqlConnection connTarget = new SqlConnection(@"Data Source=127.0.0.1;Initial Catalog=tempdb;Integrated Security=SSPI;"))
{
connTarget.Open();
// 用于数据处理的 command.
using (SqlCommand cmdProcess = new SqlCommand())
{
cmdProcess.Connection = connTarget;
//1. 创建用于存储批量处理数据的临时表
cmdProcess.CommandType = CommandType.Text;
cmdProcess.CommandText = @"CREATE TABLE #tb(id int, name nvarchar(50));";
cmdProcess.ExecuteNonQuery();
//2. 将批量处理所需要的数据导入临时表
using (var bulkCopy = new SqlBulkCopy(connTarget))
{
bulkCopy.BatchSize = 10000;
bulkCopy.BulkCopyTimeout = 9000;
// bulkCopy.NotifyAfter = 10000; // 如果要确认批量写入进度,可以设置这个,并且通过 SqlRowsCopied 事件得到进度信息
bulkCopy.DestinationTableName = "#tb"; //批量导入的目标表,注意与前面创建临时表的语句中的临时表名一致
bulkCopy.WriteToServer(tbSrouce); // 将指定的数据写入目标表
}
//3. 使用批量数据做后续处理,这些仅查询记录数
//如果是存储过程中使用,则直接在存储过程中访问临时表即可
cmdProcess.CommandText = "SELECT COUNT(*) FROM #tb;";
Console.WriteLine(cmdProcess.ExecuteScalar());
}
Console.ReadLine();
}
}
}
}
USE tempdb;
GO
SELECT TOP 500000
id = IDENTITY(int, 1, 1),
A.*
INTO DBO.tb
FROM sys.all_columns A WITH(NOLOCK),
sys.all_columns B WITH(NOLOCK)
GO
ALTER TABLE dbo.tb
ADD
PRIMARY KEY(
id
);
CREATE INDEX IX_column_id
ON dbo.tb(
column_id
);
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- 推荐的方式(性能开销比较稳定)
WITH
ID AS(
SELECT
__row_id = ROW_NUMBER() OVER(order by id DESC),
id
FROM dbo.tb WITH(NOLOCK)
WHERE column_id = 1
)
SELECT
ID.__row_id,
DATA.*
FROM ID
INNER JOIN dbo.tb DATA WITH(NOLOCK)
ON DATA.id = ID.id
WHERE ID.__row_id BETWEEN 5000 AND 5100
;
GO
-- 传统的方式
WITH
DATA AS(
SELECT
__row_id = ROW_NUMBER() OVER(order by id DESC),
*
FROM dbo.tb WITH(NOLOCK)
WHERE column_id = 1
)
SELECT *
FROM DATA
WHERE __row_id BETWEEN 5000 AND 5100
;
GO
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
GO
DROP TABLE dbo.tb;