SQLServer使用表值参数，高性能批量插入数据

记得前段时间帮同事写了个解析账号并入库的小工具，来批量导入账号信息，账号量相当大，程序每读取一条记录便执行一次insert来插入数据，整整跑了一下午才把账号全部入库。

今天又接到同事类似的需求，不过这次的账号量更大，考虑到上次遇到的问题，这次打算采用某种方案来提高插入数据的性能。

了解了下SQLServer批量插入数据的技术，主要有两种：Bulk和表值参数(SQLServer 2008的特性)，这两种方式相比循环使用insert插入数据，效率和性能明显上升。使用表值参数带来的提升更为显著。

使用表值参数插入数据的一个例子

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.IO;
using System.Collections;
using NPOI.HSSF.UserModel;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;

namespace Account2DB
{
    class Program
    {
        public static void TableValuedToDB(DataTable dt)
        {
            SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString);
            const string TSqlStatement =
             "insert into NewAccount (Account,Ting,Jie)" +
             " SELECT nc.Account, nc.Ting,nc.Jie" +
             " FROM @BulkTvp AS nc";

            SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);
            SqlParameter catParam = cmd.Parameters.AddWithValue("@BulkTvp", dt);
            catParam.SqlDbType = SqlDbType.Structured;
            //表值参数的名字叫BulkUdt，在上面的建立测试环境的SQL中有。  
            catParam.TypeName = "dbo.BulkAccount";
            try
            {
                sqlConn.Open();
                if (dt != null && dt.Rows.Count != 0)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                sqlConn.Close();
            }
        }

        public static DataTable GetTableSchema()
        {
            DataTable dt = new DataTable();
            dt.Columns.AddRange(new DataColumn[]{  
              new DataColumn("Account",typeof(string)),  
              new DataColumn("Ting",typeof(DateTime)),  
              new DataColumn("Jie",typeof(DateTime))});
            return dt;
        }  

        static void Main(string[] args)
        {
            Logger.Info("正在插入数据，请稍后...");
            try
            {
                StreamReader sr = new StreamReader(args[0], Encoding.Default);
                StringBuilder error = new StringBuilder();
                DataTable dt = GetTableSchema();
                int count = 0;
                while (!sr.EndOfStream)
                {
                    string acc = sr.ReadLine();
                    if (acc.StartsWith("账号") || acc.StartsWith("---") || String.IsNullOrEmpty(acc))
                    {
                        continue;
                    }
                    var data = acc.Split(',');
                    if (data.Count() != 3)
                    {
                        Logger.Error("错误数据：" + acc);
                        error.AppendLine(acc);
                    }
                    DataRow r = dt.NewRow();
                    r[0] = data[0].Trim();
                    r[1] = Convert.ToDateTime(data[1]);
                    r[2] = Convert.ToDateTime(data[2]);
                    dt.Rows.Add(r);
                    count++;
                    // 每一万个账号作为一批账号插入
                    if (count % 10000 == 0)
                    {
                        TableValuedToDB(dt);
                        Logger.Info("已插入：" + count);
                        dt.Clear();
                    }
                }
                // 插入剩余账号
                TableValuedToDB(dt);
                Logger.Info("已插入：" + count);

                sr.Close();
                File.AppendAllText(Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "error.txt"), error.ToString(), Encoding.Default);
            }
            catch (Exception ex)
            {
                Logger.Error(ex.ToString());
            }
            Logger.Info("执行结束，按任意键关闭...");
            Console.ReadLine(); 
        }
    }
}