.NET 批量插入数据，先查后插，性能优化

最新推荐文章于 2022-08-15 19:58:10 发布

kinginblue

最新推荐文章于 2022-08-15 19:58:10 发布

阅读量3.5k

点赞数 2

分类专栏： .NET 文章标签： DataTable select 优化 SQlBlukCopy

本文链接：https://blog.csdn.net/kinginblue/article/details/39958973

版权

.NET 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文写的背景是一次批量Insert 5000~10000条记录到数据库表，每一条数据插入前要去查是否与原有数据重复，重复则不插入。

本文就当记录这个过程。过程挺痛苦的。第一次使用索引、第一次使用存储过程，所以记录的比较啰嗦。见谅。

第一部分，先不谈“插入前查是否重复”问题。就拿批量插入来说，使用最原本的一次insert一条效率太低，即使使用存储过程效率也没提高多少。

后来看到这篇文章【http://www.cnblogs.com/wlb/archive/2010/03/02/1676136.html】，尝试使用SQlBlukCopy来批量insert数据。

·代码创建DataTable：

private DataTable getTagetDataTabel()
        {
            DataTable dt = new DataTable("temp_dt");
            dt.Columns.Add("jlh", typeof(string));
            dt.Columns.Add("rq", typeof(string));            
            dt.Columns.Add("file_name", typeof(string));
            dt.Columns.Add("deal_result", typeof(string));
            dt.Columns.Add("lock", typeof(string));
            dt.Columns.Add("file_id", typeof(int));
            dt.Columns.Add("indexx", typeof(string));//新增索引字段：抽取记录关键信息作为索引
            dt.Columns.Add("sql", typeof(string));
            return dt;
        }

·给创建的DataTable添加行数据：

for (int i = 1; i < length; i++)
            { 
                //数据来源操作
                dt.Rows.Add(new object[]
                { 
                    jlh,
                    rq,                    
                    "0",
                    "0",
                    file_id,
                    indexx,
                    ab.ToString()
                });
                totalCount++;
            }

·为了后面的数据筛选效率，给DataTable设置主键非常重要！：

//延迟至此处设置主键的原因是：装好数据再设主键，效率会比较高
            dt.PrimaryKey = new DataColumn[] { dt.Columns["indexx"] };

·设置映射关系，一次批量写进数据库

//2.设置映射，并执行（这种方式效率很高，4000条平均300毫秒）
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
                {
                    try
                    {
                        bulkCopy.DestinationTableName = "dt_citizencard_trade_data";//设置目标表名
                        bulkCopy.ColumnMappings.Add("jlh", "jlh");//设置表字段映射
                        bulkCopy.ColumnMappings.Add("rq", "rq");                        
                        bulkCopy.ColumnMappings.Add("deal_result", "deal_result");
                        bulkCopy.ColumnMappings.Add("lock", "lock");
                        bulkCopy.ColumnMappings.Add("file_id", "file_id");
                        bulkCopy.ColumnMappings.Add("indexx", "indexx");
                        connection.Open();
                        bulkCopy.WriteToServer(dt);
                        return dt.Rows.Count;
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }

第一部分完结。重要讲的就是代码创建DataTable和使用SqlBlukCopy

第二部分：先查后插的优化

当库里数据量较少的时候，每一条记录都先查后插费时一般般（4000条记录先查后插用了4秒）；当库里数据量达到10W时，直接先查后插就像噩梦一样了。

使用存储过程去查，也没什么效果。后来索引出场了！因为表数据除了不带信息的自增ID外，没有能唯一标识记录信息的字段了。故改变表格式，新增一个名为indexx的字段，插入前按数据规律抽取每条记录的信息组成唯一的记录标识，这样就不用全字段去COUNT(*)去判断是否已有重复记录，只查这个字段就行了。

然后在这个indexx字段上建立索引，这一步的贡献的效率非常高！

现在的情况是：索引+存储过程。

·创建索引的语法是：

CREATE [ VIRTUAL ] [ UNIQUE ] [ CLUSTERED ] INDEX index-name
ON [ owner.]table-name
( column-name [ ASC | DESC ], ...
    | function-name ( argument [ ,... ] ) AS column-name )
[ { IN | ON } dbspace-name ]

其中 CLUSTERED 和NONCLUSTERED 表示聚集索引和非聚集索引，详细要另行百度。

·我创建的索引：

CREATE CLUSTERED INDEX my_indexx ON dt_citizencard_trade_data (indexx)

·我建的存储过程：

CREATE PROCEDURE [dbo].[queryIndexx]
@indexx VARCHAR(29),--这个是参数
--@reval01 VARCHAR(30) output, -- 这个是输出
--@reval02 VARCHAR(30) output --输出可以有多个
AS
DECLARE @exits int
SELECT @exits=COUNT(*) FROM dt_citizencard_trade_data
WHERE indexx=@indexx AND deal_result='0'
IF(@exits<1)
BEGIN
RETURN 0 --这个是返回值
END
ELSE
BEGIN
RETURN 1 <pre name="code" class="sql" style="font-size: 14px; line-height: 26px;">--这个是返回值

ENDGO

存储过程可以带参数、有输出，有返回值。（上面两个注释仅仅是为了说明，我的代码用不到输出）

·顺便把C#调用存储过程也写上（创建参数、设置参数属性，传参执行存储过程，获取返回值/输出）：

//创建参数
                SqlParameter[] paramss = 
                {
                    new SqlParameter("@indexx", SqlDbType.VarChar, 29),
                    new SqlParameter("@re_count", SqlDbType.Int),//新增一个作为返回值
                    //new SqlParameter("@reval01", SqlDbType.VarChar, 30),//作为输出
                    //new SqlParameter("@reval02", SqlDbType.VarChar, 30)//作为输出
                };
                //设置参数属性
                paramss[0].Value = dt.Rows[i]["indexx"];
                paramss[1].Direction = ParameterDirection.ReturnValue;//设置为返回值
                //paramss[n].Direction = ParameterDirection.Output;//设置为输出
                //paramss[m].Direction = ParameterDirection.Output;//设置为输出

·调用存储过程、获取返回值和输出值：

private bool queryProcedure(string procedueNname, SqlParameter[] parameters)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    try
                    {
                        cmd.Connection = connection;
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.CommandText = procedueNname;
                        foreach (IDataParameter p in parameters)
                        {
                            cmd.Parameters.Add(p);
                        }
                        connection.Open();
                        cmd.ExecuteNonQuery();//这里调用返回的是影响行数
                        if (int.Parse(parameters[1].Value.ToString()) == 1)//这里获取的是return返回值
                        {
                            return true;
                        }
                        return false;
                        //string output_n = parameters[n].Value.ToString();//获取output输出值(示例代码)
                        //string output_m = parameters[m].Value.ToString();//获取output输出值(示例代码)
                    }
                    catch (System.Data.SqlClient.SqlException e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

以上是存储过程的主要用法了（创建参数、设置参数属性，传参执行存储过程，获取返回值/输出）。

·关键的地方到了。先查后插，思路是把所有要插的记录读到上面内存里创建的Datatable里，然后遍历这个Datatable获取每一条待插记录的indexx标识信息，调用上面准备好的存储过程，如果库里有这条记录了，则从Datatable里删除，这样筛选之后剩下就是一个完全不合库里记录重复的DataTable了，就可以使用SqlBlukCopy一次写进数据库。

这里有三个陷阱，发生在对Datatable动态删除的过程。看代码应该很容易明白吧（三个旧版的查重复代码，还没用到存储过程）：

//1.先查数据库，筛掉dt里与库里重复的//这样会报错：集合已修改；枚举操作可能无法执行
            //foreach (DataRow dr in dt.Rows)
            //{
            //    if (checkSql(dr["sql"].ToString()))
            //    {
            //        dt.Rows.Remove(dr);
            //    }
            //}

            //2.先查数据库，筛掉dt里与库里重复的//这样是不行的，删了记录saveDt的行数会减少，出现边界溢出
            //int length = dt.Rows.Count;
            //DataTable saveDt = dt.Copy();//用Clone是不行的，只克隆了框架
            //for (int i = 0; i < length; i++)
            //{
            //    if (checkSql(dt.Rows[i]["sql"].ToString()))
            //    {
            //        saveDt.Rows.RemoveAt(i);//删了记录saveDt的行数会减少，出现边界溢出
            //    }
            //}

            //3.先查数据库，筛掉dt里与库里重复的
            //int length = dt.Rows.Count;
            //DataTable saveDt = dt.Copy();//Clone只是复制框架；Copy是复制框架和数据
            //HttpContext.Current.Response.Write("dt.Rows.Count:" + dt.Rows.Count);
            //HttpContext.Current.Response.Write("saveDt.Rows.Count:" + saveDt.Rows.Count);
            //HttpContext.Current.Response.End();
            //foreach (DataRow dr in dt.Rows)
            //{
            //    if (checkSql(dr["sql"].ToString()))
            //    {
            //        saveDt.Rows.Remove(dr);//这样是不现实的，因为在两个dataTable中dr都不是同一个对象了！
            //    }
            //}

·最后这样子实现（重点是DataTable动态删除记录部分，这里新版代码改用存储过程查重复）：

//33.先查数据库，筛掉dt里与库里重复的//使用存储过程+索引
            int length = dt.Rows.Count;
            DataTable saveDt = dt.Copy();//Clone只是复制框架；Copy是复制框架和数据
            for (int i = 0; i < length; i++)
            {
                //创建参数
                SqlParameter[] paramss = 
                {
                    new SqlParameter("@indexx", SqlDbType.VarChar, 29),
                    new SqlParameter("@re_count", SqlDbType.Int),//新增一个作为返回值
                    //new SqlParameter("@reval01", SqlDbType.VarChar, 30),//作为输出(示例代码)
                    //new SqlParameter("@reval02", SqlDbType.VarChar, 30)//作为输出(示例代码)
                };
                //设置参数属性
                paramss[0].Value = dt.Rows[i]["indexx"];
                paramss[1].Direction = ParameterDirection.ReturnValue;//设置为返回值
                //paramss[n].Direction = ParameterDirection.ReturnValue;//设置为输出(示例代码)
                //paramss[m].Direction = ParameterDirection.Output;//设置为输出(示例代码)

                if (queryProcedure("queryIndexx", paramss))
                {
                    DataRow[] drs = saveDt.Select("indexx='" + dt.Rows[i]["indexx"] + "'");//这部分代码效率太低了！！给dt设置主键/改成Find（）方法，
                    foreach (DataRow drr in drs)
                    {
                        saveDt.Rows.Remove(drr);
                    }
                }
            }

好了，可以动态删除DataTable里的记录了。现在，遍历删除的性能瓶颈不是查数据库了（因为使用了索引+存储过程），而是 Datatable.Select()方法！感谢这篇文章【http://www.cnblogs.com/yangecnu/archive/2013/05/20/3087896.html】

要点1 是用Datatable.Row.Find()方法而不是Datatable.Select()方法，这样要看个人需求。

要点2 是给装载好数据的Datatable设置主键为indexx！这样效率真的一下实现社会主义啊！

要点3 是DataTable先装数据后建主键，这是特性。原作者说：“在填充数据之前创建主键，然后填充数据，比填充数据完之后创建主键消耗的时间要多。这是由于，创建主键后，再向其中添加数据，会导致需要重新生成索引，这和数据库中，不适合在频繁变动的字段上创建主键的原理是一样的”。很好理解。

所以本文开始那里有一段代码是这样的：（就是为了这个）：

//延迟至此处设置主键的原因是：装好数据再设主键，效率会比较高
            dt.PrimaryKey = new DataColumn[] { dt.Columns["indexx"] };

好像讲完了。

主要思路是把所有内待插入数据读进内存的Datatable，然后遍历这个DataTable查重，筛选完后SqlBlukCopy一次写入数据库。

整体看来效率瓶颈有三个地方：插入、查重、Datatable.Select()

使用SqlBlukCopy可以解决批量插入效率问题。使用索引+存储过程提升查重效率。给内存里的DataTable建主键解决DataTable.Select()的效率。

第一次这么认真写。今后得坚持才行。

kinginblue

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录