實戰(zhàn)手記：讓百萬級數(shù)據(jù)瞬間導(dǎo)入SQL Server

1、實戰(zhàn)手記：讓百萬級數(shù)據(jù)瞬間導(dǎo)入sql server想必每個dba都喜愛挑戰(zhàn)數(shù)據(jù)導(dǎo)入時光，用時越短工作效率越高，也充分的能夠證實自己的實力。實際工作中有時候需要把大量數(shù)據(jù)導(dǎo)入數(shù)據(jù)庫，然后用于各種程序計算，本文將向大家推舉一個挑戰(zhàn)4秒極限讓百萬級數(shù)據(jù)眨眼導(dǎo)入sql server試驗案例。 本試驗將用法5中辦法完成這個過程，并具體記錄各種辦法所耗費的時光。所用到工具為vial studio 2008和sql server 2000、sql server 2008，分離用法5中辦法將100萬條數(shù)據(jù)導(dǎo)入sql server 2000與sql server 2008中，試驗環(huán)境是dell 2850雙2.0

2、gcpu，2g內(nèi)存的服務(wù)器。感愛好的伴侶可以下載源代碼自己驗證一下所用時光。 好了，下面我們分離用法基本的insert 語句、用法bulk insert語句、在多線程中用法bulk insert、用法sqlbulkcopy類、在多線程中用法sqlbulkcopy類五種辦法，挑戰(zhàn)4秒極限。還要有一點需要舉行解釋，本試驗中執(zhí)行sql語句的地方用法了isline framework框架中的dataprover模塊，這個模塊只是對sql配置的讀取和封裝，并不會對終于結(jié)果有本質(zhì)性的影響，關(guān)于isline framework框架方面的學問，請參考“isline framework”框架系列文章。 數(shù)據(jù)庫方

3、面用法sql server 2000與sql server 2008，表名tableb，字段名稱為value1，數(shù)據(jù)庫名可以在app.config中修改，默認為test。 辦法一.用法基本的insert 語句 這種辦法是最基本的辦法，大多數(shù)人一開頭都會想到這種辦法。但是insert語句 乎并不適合大批量的操作，是不是這樣呢? 本辦法中將100萬數(shù)據(jù)分為10個批次，每個批次10萬條，每10萬條1個事務(wù)，分10次導(dǎo)入數(shù)據(jù)庫。 - 基本語句: insert into tableb (value1) values (” i ”); 解釋：語句中的i是宿主程序中的一個累加變量，用于填充數(shù)據(jù)庫字段中的 。

4、 sql server 2000 耗時：901599 sql server 2008耗時：497638 辦法二.用法bulk insert語句 這個類的效果，在本試驗中可以說是最令人愜意的了，它的用法最簡便、靈便，速度很快。 “bulk insert”語句 乎不是很常用， aicken聽說oracle中有一種可以將外部文件映射為oracle暫時表，然后挺直將暫時表中的數(shù)據(jù)導(dǎo)入oracle其他表中的辦法，這種辦法的速度十分令人愜意，sql server的bulk insert是不是同樣令人愜意呢? - 基本語句： bulk insert tableb from ' c:sql.txt&#

5、39; with (fieldterminator = ',',rowter /.,mbminator='|',batchsize = 100000) 解釋：“c:sql.txt”是一個預(yù)先生成的包含100條數(shù)據(jù)的文件，這些數(shù)據(jù)以“|”符號分隔，每10萬條數(shù)據(jù)一個事務(wù)。 sql server 2000耗時：4009 sql server 2008耗時：10722 辦法三.在多線程中用法bulk insert 在辦法二的基礎(chǔ)上，將100萬條數(shù)據(jù)分五個線程，每個線程負責20萬條數(shù)據(jù)，每5萬條一個事物，五個線程同時啟動，看看這樣的效果吧。 sql server 200

6、0耗時：21099 sql server 2008耗時：10997 辦法四.用法sqlbulkcopy類 這種辦法速度也很快，但是要依靠內(nèi)存，對于幾千萬條、多字段的復(fù)雜數(shù)據(jù)，可能在內(nèi)存方面會有較大的消耗，不過可以用法64位解決計劃處理這個問題。 幾千萬條、多字段的數(shù)據(jù)的狀況普通在一些業(yè)務(wù)場景中會碰到，比如計算全球消費者某個業(yè)務(wù)周期消費額時，要先獲得主數(shù)據(jù)庫表中的會員消費記錄快照，并將快照儲存至暫時表中，然后供計算程序用法這些數(shù)據(jù)。并且有些時候消費者的消費數(shù)據(jù)并不在一臺數(shù)據(jù)庫服務(wù)器中，而是來自多個國家的多臺服務(wù)器，這樣我們就必需借助內(nèi)存或外存設(shè)備中轉(zhuǎn)這些數(shù)據(jù)，然后清洗、合并、檢測，最后導(dǎo)入專用表

7、供計算程序用法。 基本語句： using (system.data.sqlclient.sqlbulkcopy sqlbc = new system.data.sqlclient.sqlbulkcopy(conn) sqlbc.batchsize = 100000; sqlbc.bulkcopytimeout = 60; sqlbc.destinationtablename = dbo.tableb sqlbc.columnmaps.a( valuea , value1 sqlbc.writetoserver(dt); 解釋： batchsize = 100000; 指示每10萬條一個事務(wù)并提交 bulkcopytimeout = 60; 指示60秒按超時處理 destinationtablename = dbo.tableb 指示將數(shù)據(jù)導(dǎo)入tableb表 columnmappings.add( valuea , value1 指示將內(nèi)存中valuea字段與tableb中的value1字段匹配 writetoserver(dt);寫入數(shù)據(jù)庫。其中dt是預(yù)先構(gòu)建好的datatable，其中包含valuea字段。 sql server 2000耗時：4989 sql server 2008耗時：10412 辦法五.在多線