本篇，我將來講解一下在sqlserver中批量插入數據。
先創建一個用來測試的數據庫和表，為了讓插入數據更快，表中主鍵采用的是guid，表中沒有創建任何索引。guid必然是比自增長要快的，因為你生成一個guid算法所花的時間肯定比你從數據表中重新查詢上一條記錄的id的值然后再進行加1運算要少。而如果存在索引的情況下，每次插入記錄都會進行索引重建，這是非常耗性能的。如果表中無可避免的存在索引，我們可以通過先刪除索引，然后批量插入，最后再重建索引的方式來提高效率。
|
1
2
3
4
5
6
7
8
9
|
create database carsys; go use carsys; go create table product(id uniqueidentifier primary key,name varchar(50) not null,price decimal(18,2) not null) |
我們通過sql腳本來插入數據，常見如下三種方式。
方式一，一條一條插入，性能最差，不建議使用。
|
1
2
3
|
insert into product(id,name,price) values(newid(),'牛欄1段',160);insert into product(id,name,price) values(newid(),'牛欄2段',260);...... |
方式二
|
1
2
3
4
|
insert into product(id,name,price) values(newid(),'牛欄1段',160),(newid(),'牛欄2段',260)...... |
方式三
|
1
2
3
4
5
6
|
insert into product(id,name,price) select newid(),'牛欄1段',160 union all select newid(),'牛欄2段',180 union all...... |
在c#中通過ado.net來實現批量操作同樣也存在三種方式。
方式一：逐條插入
|
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
|
static void insertone() { console.writeline("采用一條一條插入的方式實現"); stopwatch sw = new stopwatch(); long totalrow = 1000000; using (sqlconnection conn = new sqlconnection(strconnmsg)) //using中會自動open和close 連接。 { string sql = "insert into product(id,name,price) values(newid(),@p,@d)"; conn.open(); for (int i = 0; i < 1000000; i++) { using (sqlcommand cmd = new sqlcommand(sql, conn)) { cmd.parameters.addwithvalue("@p", "商品" + i); cmd.parameters.addwithvalue("@d", i); sw.start(); cmd.executenonquery(); console.writeline(string.format("插入1條記錄，時間：{0}", sw.elapsedmilliseconds)); } if (i == 1000) { sw.stop(); break; } } } console.writeline(string.format("插入{0}條記錄，每1000條的插入時間是{1}毫秒,預估總得插入時間是{2}毫秒，{3}分鐘", totalrow, sw.elapsedmilliseconds,((sw.elapsedmilliseconds / 1000) * totalrow), getminute((sw.elapsedmilliseconds / 1000 * totalrow)))); } |
運行結果如下：
我們會發現插入100w條記錄，預計需要50分鐘時間，每插入一條記錄大概需要3毫秒左右。
方式二：使用sqlbulk
|
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
|
#region 方式二static void inserttwo(){ console.writeline("使用bulk插入的實現方式"); stopwatch sw = new stopwatch(); datatable dt = gettableschema(); using (sqlconnection conn = new sqlconnection(strconnmsg)) { sqlbulkcopy bulkcopy = new sqlbulkcopy(conn); bulkcopy.destinationtablename = "product"; bulkcopy.batchsize = dt.rows.count; conn.open(); sw.start(); for (int i = 0; i < totalrow;i++ ) { datarow dr = dt.newrow(); dr[0] = guid.newguid(); dr[1] = string.format("商品", i); dr[2] = (decimal)i; dt.rows.add(dr); } if (dt != null && dt.rows.count != 0) { bulkcopy.writetoserver(dt); sw.stop(); } console.writeline(string.format("插入{0}條記錄共花費{1}毫秒，{2}分鐘", totalrow, sw.elapsedmilliseconds, getminute(sw.elapsedmilliseconds))); }}static datatable gettableschema(){ datatable dt = new datatable(); dt.columns.addrange(new datacolumn[] { new datacolumn("id",typeof(guid)), new datacolumn("name",typeof(string)), new datacolumn("price",typeof(decimal))}); return dt;}#endregion |
運行結果如下：
插入100w條記錄才8s多，是不是很溜。
方式三：使用tvps(表值參數）插入數據
從sqlserver 2008起開始支持tvps。創建緩存表producttemp ，執行如下sql。
|
1
2
3
4
5
|
create type producttemp as table(id uniqueidentifier primary key,name varchar(50) not null,price decimal(18,2) not null) |
執行完成之后，會發現在數據庫carsys下面多了一張緩存表producttemp
可見插入100w條記錄共花費了11秒多。
總結：大數據批量插入方式一盡量避免使用，而方式二和方式三都是非常高效的批量插入數據方式。其都是通過構建datatable的方式插入的，而我們知道datatable是存在內存中的，所以當數據量特別特別大，大到內存中無法一次性存儲的時候，可以分段插入。比如需要插入9千萬條數據，可以分成9段進行插入，一次插入1千萬條。而在for循環中直接進行數據庫操作，我們是應該盡量避免的。每一次數據庫的連接、打開和關閉都是比較耗時的，雖然在c#中存在數據庫連接池，也就是當我們使用using或者conn.close()，進行釋放連接時，其實并沒有真正關閉數據庫連接，它只是讓連接以類似于休眠的方式存在，當再次操作的時候，會從連接池中找一個休眠狀態的連接，喚醒它，這樣可以有效的提高并發能力，減少連接損耗。而連接池中的連接數，我們都是可以配置的。
源碼下載：https://pan.baidu.com/s/1slkrrlr
以上就是本文的全部內容，希望對大家的學習有所幫助，也希望大家多多支持服務器之家。
原文鏈接：http://www.cnblogs.com/jiekzou/p/6145550.html
