本篇，我將來講解一下在sqlserver中批量插入數(shù)據(jù)。
先創(chuàng)建一個(gè)用來測(cè)試的數(shù)據(jù)庫和表，為了讓插入數(shù)據(jù)更快，表中主鍵采用的是guid，表中沒有創(chuàng)建任何索引。guid必然是比自增長(zhǎng)要快的，因?yàn)槟闵梢粋€(gè)guid算法所花的時(shí)間肯定比你從數(shù)據(jù)表中重新查詢上一條記錄的id的值然后再進(jìn)行加1運(yùn)算要少。而如果存在索引的情況下，每次插入記錄都會(huì)進(jìn)行索引重建，這是非常耗性能的。如果表中無可避免的存在索引，我們可以通過先刪除索引，然后批量插入，最后再重建索引的方式來提高效率。
|
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腳本來插入數(shù)據(jù)，常見如下三種方式。
方式一，一條一條插入，性能最差，不建議使用。
|
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來實(shí)現(xiàn)批量操作同樣也存在三種方式。
方式一：逐條插入
|
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("采用一條一條插入的方式實(shí)現(xiàn)"); stopwatch sw = new stopwatch(); long totalrow = 1000000; using (sqlconnection conn = new sqlconnection(strconnmsg)) //using中會(huì)自動(dòng)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條記錄，時(shí)間：{0}", sw.elapsedmilliseconds)); } if (i == 1000) { sw.stop(); break; } } } console.writeline(string.format("插入{0}條記錄，每1000條的插入時(shí)間是{1}毫秒,預(yù)估總得插入時(shí)間是{2}毫秒，{3}分鐘", totalrow, sw.elapsedmilliseconds,((sw.elapsedmilliseconds / 1000) * totalrow), getminute((sw.elapsedmilliseconds / 1000 * totalrow)))); } |
運(yùn)行結(jié)果如下：
我們會(huì)發(fā)現(xiàn)插入100w條記錄，預(yù)計(jì)需要50分鐘時(shí)間，每插入一條記錄大概需要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插入的實(shí)現(xiàn)方式"); 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}條記錄共花費(fèi){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 |
運(yùn)行結(jié)果如下：
插入100w條記錄才8s多，是不是很溜。
方式三：使用tvps(表值參數(shù)）插入數(shù)據(jù)
從sqlserver 2008起開始支持tvps。創(chuàng)建緩存表producttemp ，執(zhí)行如下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) |
執(zhí)行完成之后，會(huì)發(fā)現(xiàn)在數(shù)據(jù)庫carsys下面多了一張緩存表producttemp
可見插入100w條記錄共花費(fèi)了11秒多。
總結(jié)：大數(shù)據(jù)批量插入方式一盡量避免使用，而方式二和方式三都是非常高效的批量插入數(shù)據(jù)方式。其都是通過構(gòu)建datatable的方式插入的，而我們知道datatable是存在內(nèi)存中的，所以當(dāng)數(shù)據(jù)量特別特別大，大到內(nèi)存中無法一次性存儲(chǔ)的時(shí)候，可以分段插入。比如需要插入9千萬條數(shù)據(jù)，可以分成9段進(jìn)行插入，一次插入1千萬條。而在for循環(huán)中直接進(jìn)行數(shù)據(jù)庫操作，我們是應(yīng)該盡量避免的。每一次數(shù)據(jù)庫的連接、打開和關(guān)閉都是比較耗時(shí)的，雖然在c#中存在數(shù)據(jù)庫連接池，也就是當(dāng)我們使用using或者conn.close()，進(jìn)行釋放連接時(shí)，其實(shí)并沒有真正關(guān)閉數(shù)據(jù)庫連接，它只是讓連接以類似于休眠的方式存在，當(dāng)再次操作的時(shí)候，會(huì)從連接池中找一個(gè)休眠狀態(tài)的連接，喚醒它，這樣可以有效的提高并發(fā)能力，減少連接損耗。而連接池中的連接數(shù)，我們都是可以配置的。
源碼下載：https://pan.baidu.com/s/1slkrrlr
以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持服務(wù)器之家。
原文鏈接：http://www.cnblogs.com/jiekzou/p/6145550.html
