【SQL Server】7种插入方式的效率对比（史上最全）

最新推荐文章于 2023-02-21 15:08:33 发布

羽木落凡

最新推荐文章于 2023-02-21 15:08:33 发布

阅读量5.8k

点赞数 3

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/wsadcg/article/details/108221137

版权

SQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

关于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级就略显逊色。因此表值参数在低数量级的更新操作中表现更好。

根据以上所有试验结果，对各类插入方式的应用场景作以下总结：

在这里插入图片描述

羽木落凡

关注

3
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
【SQL Server】7种插入方式的效率对比（史上最全）

关于Sqlserver的插入方法已经了解很多，于是打算好好整理一下，并做一个性能上的综合对比。试验准备：数据库版本：SqlServer 2008 R2创建数据库结构如下：CREATE TABLE [UserInfo](ID int,UserName nvarchar(50),Pwd varchar(50))试验过程：一、正常逐条插入方式1K条和10W条数据的执行结果如下：执行代码如下：Console.WriteLine("逐条插入法");Stopwatch sw = Stopwa
复制链接

扫一扫