来自 编程应用 2019-09-26 04:01 的文章
当前位置: 六合联盟网 > 编程应用 > 正文

批量插入

最近要做.net关于sql大量插入,找到了sqlbulkcopy(自己google下,应该很多说明了)这个好东西,于是测试下性能,用了三个方法对比:

关于SqlBulkCopy的测试

最近要做.net关于sql大量插入,找到了sqlbulkcopy(自己google下,应该很多说明了)这个好东西,于是测试下性能,用了三个方法对比:

1)直接用ado.net,for循环N次进行单条插入

2)把N条插入语句拼在一个sql,进行插入

3)直接使用sqlbulkcopy进行插入

代码如下:

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
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
 
namespace SQLTEST
{
    class Program
    {
        static void Main(string[] args)
        {
 
            //int time = 200;
            test(20);
            test(200);
            test(2000);
            test(10000);
            test(50000);
            Console.ReadLine();
        }
 
        public static void test(int time){
            Stopwatch sp = new Stopwatch();
            Console.WriteLine(time + "条数据插入测试");
 
            //测试方法1
            {
                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))
                {
                    sqlcon.Open();
                    string singesql = "INSERT INTO [student] ([name],[age])VALUES('abc',3);";
                    SqlCommand sqlcommand = new SqlCommand(singesql, sqlcon);
                    //计时开始
                    sp.Restart();
                    for (int i = 0; i < time; i++)
                    {
                        sqlcommand.ExecuteNonQuery();
                    }
                    sp.Stop();
                }
            }
            Console.WriteLine("方法1:" + sp.ElapsedMilliseconds + "毫秒");
 
 
 
            //测试方法2
            {
                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))
                {
                    sqlcon.Open();
                    string singesql = "INSERT INTO [student] ([name],[age])VALUES('abc',3);";
                    string execsql = "";
                    for (int i = 0; i < time; i++)
                    {
                        execsql = execsql + singesql;
                    }
                    SqlCommand sqlcommand = new SqlCommand(execsql, sqlcon);
 
                    //计时开始
                    sp.Restart();
                    sqlcommand.ExecuteNonQuery();
                    sp.Stop();
                }
            }
            Console.WriteLine("方法2:" + sp.ElapsedMilliseconds + "毫秒");
 
 
            //测试方法3
            {
                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))
                {
                    sqlcon.Open();
                    SqlBulkCopy sqlc = new SqlBulkCopy(sqlcon);
                    DataTable dt = new DataTable();
                    dt.Columns.Add("id");
                    dt.Columns.Add("name");
                    dt.Columns.Add("age");
 
                    for (int i = 0; i < time; i++)
                    {
                        dt.Rows.Add(38009, "nemw", 123);
                    }
                    sqlc.DestinationTableName = "student";
 
                    //计时开始
                    sp.Restart();
                    sqlc.WriteToServer(dt);
                    sp.Stop();
                }
            }
            Console.WriteLine("方法3:" + sp.ElapsedMilliseconds + "毫秒");
            Console.WriteLine();
         
        }
    }
}

  

插入N条数据的测试结果如下:

图片 1

效率相差还是很夸张的,大家大量数据插入还是有sqlbulkcopy吧,原理还的高手告知~~

1)直接用ado.net,for循环N次进行单条插入

2)把N条插入语句拼在一个sql,进行插入

3)直接使用sqlbulkcopy进行插入

代码如下:

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Data;using System.Data.SqlClient;using System.Diagnostics;namespace SQLTEST{    class Program    {        static void Main(string[] args)        {            //int time = 200;            test;            test;            test;            test;            test;            Console.ReadLine();        }        public static void test{            Stopwatch sp = new Stopwatch();            Console.WriteLine(time + "条数据插入测试");            //测试方法1            {                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))                {                    sqlcon.Open();                    string singesql = "INSERT INTO [student] ([name],[age])VALUES;";                    SqlCommand sqlcommand = new SqlCommand(singesql, sqlcon);                    //计时开始                    sp.Restart();                    for (int i = 0; i < time; i++)                    {                        sqlcommand.ExecuteNonQuery();                    }                    sp.Stop();                }            }            Console.WriteLine("方法1:" + sp.ElapsedMilliseconds + "毫秒");            //测试方法2            {                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))                {                    sqlcon.Open();                    string singesql = "INSERT INTO [student] ([name],[age])VALUES;";                    string execsql = "";                    for (int i = 0; i < time; i++)                    {                        execsql = execsql + singesql;                    }                    SqlCommand sqlcommand = new SqlCommand(execsql, sqlcon);                    //计时开始                    sp.Restart();                    sqlcommand.ExecuteNonQuery();                    sp.Stop();                }            }            Console.WriteLine("方法2:" + sp.ElapsedMilliseconds + "毫秒");            //测试方法3            {                using (SqlConnection sqlcon = new SqlConnection("Data Source=.;Initial Catalog=Test;User ID=sa;Password=??"))                {                    sqlcon.Open();                    SqlBulkCopy sqlc = new SqlBulkCopy;                    DataTable dt = new DataTable();                    dt.Columns.Add;                    dt.Columns.Add;                    dt.Columns.Add;                    for (int i = 0; i < time; i++)                    {                        dt.Rows.Add(38009, "nemw", 123);                    }                    sqlc.DestinationTableName = "student";                    //计时开始                    sp.Restart();                    sqlc.WriteToServer;                    sp.Stop();                }            }            Console.WriteLine("方法3:" + sp.ElapsedMilliseconds + "毫秒");            Console.WriteLine();                }    }}

  

插入N条数据的测试结果如下:

图片 2

效率相差还是很夸张的,大家大量数据插入还是有sqlbulkcopy吧,原理还的高手告知~~

本文由六合联盟网发布于编程应用,转载请注明出处:批量插入

关键词: