SQL Server] 七种插入方法的效率比较(史上最全7 种插入方法的效率比较(史上最全)
关于Sqlserver的插入方法已经了解很多,于是打算好好整理一下,并做一个性能上的综合对比。
试验准备:
数据库版本:SqlServer 2008 R2
创建数据库结构如下:
CREATE DATABASE TEST
GO
USE TEST
GO
CREATE TABLE [UserInfo](
ID int,
Name nvarchar(50),
Pwd varchar(50))
试验过程:
一、正常逐条插入方式
1K~100W条数据的执行结果如下:
执行代码如下:
(考虑到篇幅问题,这里只贴一组插入过程的代码,扩大数量级只需要修改外循环次数即可。后面类同)
Console.WriteLine("逐条插入法");
Stopwatch sw = Stopwatch.StartNew();
using (SqlConnection cn = new SqlConnection(connStr))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
for (int j = 0; j < 1; j++) // 外循环
{
for (int i = j * 1000; i < (j + 1) * 1000; i++) //内循环
{
cmd.CommandText = string.Format("INSERT INTO [UserInfo] VALUES ({0},'name{0}','password{0}')", i);
cmd.ExecuteNonQuery();
}
}
}
}
Console.WriteLine($"插入1000行数据的总时间:{sw.ElapsedMilliseconds} ms");
二、使用SqlCommandBuilder插入数据
我们在做表单界面开发的时候,常常需要把界面上的数据提交到数据库,通常情况下,表单控件绑定的是DataTable数据源。这种情况下,如果是再按行读取DataTable中的内容,一个个更新或插入到数据库中,不仅效率低下,而且代码也会相对复杂。因此微软提供了SqlCommandBuilder对象,借助它可以让DataTable中的内容直接提交到数据库中,系统会识别是更新还是插入动作。
注意:如果是更新操作,数据表一定要包含主键!
这里只进行插入试验:
总体来说,该方法与单条INSERT循环插入的效率相差不大。但从应用场景上,如果是基于界面操作的,当然这种方法更胜一筹。
以下是部分执行代码:
Console.WriteLine("使用SqlCommandBuilder插入数据");
Stopwatch sw = Stopwatch.StartNew();
using (SqlConnection cn = new SqlConnection(connStr))
{
using (SqlDataAdapter da = new SqlDataAdapter("SELECT TOP 0 [ID],[Name],[Pwd] FROM [UserInfo]", cn))
{
using (SqlCommandBuilder cmdBd = new SqlCommandBuilder(da))
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(String));
dt.Columns.Add("Pwd", typeof(String));
for (int j = 0; j < 1; j++) // 外循环
{
for (int i = j * 1000; i < (j + 1) * 1000; i++) // 内循环
{
dt.Rows.Add(i, $"name{i}", $"password{i}");
}
}
da.Update(dt);
}
}
}
Console.WriteLine($"插入1000行数据的总时间:{sw.ElapsedMilliseconds} ms");
从这里开始,就是神级插入法之间的对决了,与上面两种方法完全不在一个数量级。
三、采用批量插入方式(单条INSERT语句关联1000条数据,因为数据库限制)
1K条和10W条数据的执行结果如下:
可以看出,无论小批量插入还是大批量插入,都已经完胜逐条插入法!
执行代码如下:
Console.WriteLine("批量插入法");
Stopwatch sw = Stopwatch.StartNew();
StringBuilder sb = new StringBuilder();
using (SqlConnection cn = new SqlConnection(connStr))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
for (int j = 0; j < 1; j++) // 外循环
{
for (int i = j * 1000; i < (j + 1) * 1000; i++) // 内循环
{
sb.Append(string.Format("({0},'name{0}','password{0}'),", i));
}
cmd.CommandText = string.Format("INSERT INTO [UserInfo] VALUES {0}", sb.Remove(sb.Length - 1, 1));
cmd.ExecuteNonQuery();
sb.Clear();
}
}
}
Console.WriteLine($"插入1000行数据的总时间:{sw.ElapsedMilliseconds} ms");
四、采用临时表复制的方式(可以打破1000条的限制)
虽然采用临时表复制的方式可以不受插入条数限制,但一次性插入数据量过大会使得数据拥堵,甚至插入失败。因此为了对比批量插入法,这里还是用分批插入、一次1000条数据的方式来做验证。
执行结果如下:
可以看出和批量插入法的效率相差无几。
执行代码如下:
Console.WriteLine("临时表复制法");
Stopwatch sw = Stopwatch.StartNew();
StringBuilder sb = new StringBuilder();
using (SqlConnection cn = new SqlConnection(connStr))
{
cn.Open();
using (SqlCommand cmd = cn.CreateCommand())
{
for (int j = 0; j < 1000; j++) // 外循环
{
for (int i = j * 1000; i < (j + 1) * 1000; i++) // 内循环
{
sb.Append(string.Format("({0},'name{0}','password{0}'),", i));
}
cmd.CommandText = string.Format("INSERT INTO [UserInfo](ID,Name,Pwd) SELECT T.ID,T.Name,T.Pwd FROM (VALUES {0}) AS t(ID,Name,Pwd)", sb.Remove(sb.Length - 1, 1));
cmd.ExecuteNonQuery();
sb.Clear();
}
}
}
Console.WriteLine($"插入1000行数据的总时间:{sw.ElapsedMilliseconds} ms");
以上两组试验之所以没有进行100W条数据插入的展示,是因为外循环次数过多时,单次插入的执行效率有一定概率会上升到1s以上,而且一旦上升就很难降下来。这一度令我十分苦恼= =!
那么如何在大批量的情况下仍然保持高效的插入?
经过本人多次测试,也未找到完全有效的方法。目前的做法是执行小批量(大概小于100次)外循环后,将SqlCommand对象释放。测试结果也还可以接受,平均每1000条10多ms(实际上单次1000条数据最快可达3ms):
五、使用BULK INSERT从文件插入
要说插入数据,怎少的了从文件中导入。虽说SSMS提供了从文件导入数据的方式,但实际开发中少不了需要用户选择文件自己导入的情况。于是BULK INSERT便是很好的选择。
关于BULK INSERT的详细用法,给大家提供了MSDN的链接,需要的请自行参阅
BULK INSERT语法
先上一段BULK INSERT的执行代码:
Console.WriteLine("BULK INSERT法");
Stopwatch sw = Stopwatch.StartNew();
Console.WriteLine("每批次条数:1000");
using (SqlConnection cn = new SqlConnection(connStr))
{
cn.Open();
{
using (SqlCommand cmd = cn.CreateCommand())
{
cmd.CommandTimeout = 0;
cmd.CommandText = @"BULK INSERT [UserInfo] FROM 'D:\sqldata.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR ='|',BATCHSIZE = 1000)";
cmd.ExecuteNonQuery();
}
}
}
Console.WriteLine($"插入10000000行数据的总时间:{sw.ElapsedMilliseconds} ms");
其中最关键的三个参数解释如下:
FIELDTERMINATOR:字段分隔符
ROWTERMINATOR:行分隔符(注意:行分隔符不能出现在末尾)
BATCHSIZE:每批次行数(BULK会将文件中所有行进行分批导入,每批次形成一个事务,且单批次行数没有限制)
所以我的数据格式是这样的:
接下来我进行了1000W条数据在不同BATCHSIZE参数下的导入效率对比试验:
【探索的道路总是曲折而又漫长的= =】
多组数据对比可以看出,执行速率与BATCHSIZE的数值呈曲线关系,大约拐点在10W-100W之间,可能受服务器性能的影响。此外,这个成绩已经明显优于3、4两组实验,且不会存在数据量大导致的降速现象出现。
为了同后面的方法做对比,我又增加了以下三组试验,分别是1K、100W、1000W三个数量级的数据插入:
六、 使用SqlBulkCopy从内存中复制
和Bulk从文件导入数据一样,SqlBulkCopy也是微软提供给我们的大容量数据插入工具。使用起来也非常简单,甚至不需要写SQL语句,见代码如下:
public static DataTable MakeTable()
{
DataTable dt = new DataTable();
dt.Columns.Add("ID", typeof(int));
dt.Columns.Add("Name", typeof(String));
dt.Columns.Add("Pwd", typeof(String));
return dt;
}
public static void MainProcess()
{
Console.WriteLine("使用SqlBulkCopy插入");
DataTable dt = MakeTable();
for (int j = 0; j < 1; j++) // 外循环
{
for (int i = j * 1000; i < (j + 1) * 1000; i++) // 内循环
{
dt.Rows.Add(i, $"name{i}", $"password{i}");
}
}
Stopwatch sw = Stopwatch.StartNew();
using (SqlConnection cn = new SqlConnection(connStr))
{
cn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn))
{
bulkCopy.DestinationTableName = "UserInfo";
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
}
}
Console.WriteLine($"插入1000行数据的总时间:{sw.ElapsedMilliseconds} ms");
}
这里分别进行1K、100W、1000W的数据量插入试验,对比如下:
结果很明显了,完胜Bulk从文件插入的速率!
七、使用表值参数进行数据传递
表值参数是SQL Server 2008以后出现的新特性,为了弥补BULK无法实现复杂的处理逻辑的弱点。
详细说明可以参阅MSDN:表值参数
下面展示如何从客户端借助表值参数导入数据:
1)先要在数据库建立表类型:
CREATE TYPE UserInfoTableType AS TABLE
( ID int
, Name nvarchar(50)
, Pwd varchar(50));
2)使用SqlCommand的表值参数类型,将DataTable导入到数据库
执行代码如下:
Console.WriteLine("使用表值参数插入");
DataTable dt = MakeTable();
for (int j = 0; j < 1; j++) // 外循环
{
for (int i = j * 1000; i < (j + 1) * 1000; i++) // 内循环
{
dt.Rows.Add(i, $"name{i}", $"password{i}");
}
}
Stopwatch sw = Stopwatch.StartNew();
using (SqlConnection cn = new SqlConnection(connStr))
{
cn.Open();
string sql = "INSERT INTO [UserInfo] (Id,Name,Pwd)" +
" SELECT ec.Id, ec.Name,ec.Pwd" +
" FROM @Tvp AS ec";
using (SqlCommand cmd = new SqlCommand(sql, cn))
{
SqlParameter catParam = cmd.Parameters.AddWithValue("@Tvp", dt);
catParam.SqlDbType = SqlDbType.Structured;
catParam.TypeName = "dbo.UserInfoTableType";
cmd.ExecuteNonQuery();
}
}
Console.WriteLine($"插入1000行数据的总时间:{sw.ElapsedMilliseconds} ms");
执行结果如下:
100W级和BULK不相上下,但1000W级就略显逊色。因此表值参数在低数量级的更新操作中表现更好。
根据以上所有试验结果,对各类插入方式的应用场景作以下总结:
下一篇: SQL 语法