線上訂房服務-台灣趴趴狗聯合訂房中心
發文 回覆 瀏覽次數:4013
推到 Plurk!
推到 Facebook!

如何增加 MS SQL Server的寫入速度

答題得分者是:carstyc
Andy Wu
一般會員


發表:17
回覆:25
積分:18
註冊:2004-02-25

發送簡訊給我
#1 引用回覆 回覆 發表時間:2010-05-14 00:10:13 IP:220.134.xxx.xxx 訂閱
後端用 MS SQL Server 2005

前端是一個主 Thread 接收 Socket 封包資料 , 接著啟動 Multi-Thread 將資料寫回 DB 之中

寫入方式是透過 TAdoConnection TADOStoredProc , 呼叫 DB 上的 Stored Procedure 寫入至對應的 Table

同時間約有 5 個子 Thread , 各自建立各自的 TAdoConnection TADOStoredProc

呼叫寫入 DB 的函數 , 必須採用 Synchronize() , 因為寫入的順序必須等於接收的順序

因為是即時性的資料 , 所以無法收了 n 個封包後才整批寫入 , 必須一個封包就得呼叫一次 Stored Procedure

做了幾個測試的程式 , 同樣的 10 萬個封包 :

1. 1 個Thread 呼叫相同的 SP_1 , 共 10 萬次 , 寫入 Table_1 , 需要 100 秒
2. 50 個Thread 呼叫相同的 SP_1 , 共 10 萬次 , 寫入 Table_1 , 需要 100 秒
3. 1 個Thread 呼叫不同的 SP_1 , SP_2 , SP_3 , 合計總呼叫也是 10 萬次 , 寫入 Table_1 , Table_2 , Table_3 , 需要 100 秒 ( 算資料量最多的 Table )
4. 將 10 萬個封包 , 根據某條件 , 將其拆散至 3 個 TList 物件之中 , 啟動 3 個 Thread , 各自監控各自的 TList 物件 , 3 個 Thread 各自呼叫各自應該呼叫
的 SP_1 , SP_2 , SP_3 , 合計總呼叫也是 10 萬次 , 寫入 Table_1 , Table_2 , Table_3 , 需要 100 秒 ( 算資料量最多的 Table )

經過以上的測試結果 , 無論 1~n 個 Thread , 感覺上 Thread 寫入 DB 的速度都是一樣的

因為我有寫入順序=接收順序 , 且為即時性的資料 , 所以寫入 DB 的方式必然會受限

如果不管順序 , 10 萬筆資料大概 80 秒寫完

想請教各位先進 , 這樣的問題是程式設計還有改進的空間 , 還是 SQL Server 有效能調校的設定呢 ?
bestlong
站務副站長


發表:125
回覆:734
積分:506
註冊:2002-10-19

發送簡訊給我
#2 引用回覆 回覆 發表時間:2010-05-19 09:45:56 IP:60.248.xxx.xxx 未訂閱
我對 Thread 不熟, 不過因為是有序資料需要依序排隊處理
所以這不是越多 Thread 就速度越快的需求環境

要先確認你是否有自己控制 transaction?
新增一筆資料就會產生一次交易不管是直接 insert 或是呼叫 sp_1
所以 10 萬次就有 10 萬個交易, 在進行短時間大量資料存入時對資料庫會有很大的負擔
不知道你選擇用 stored procedure 的原因為何?
是認為 stored procedure 速度會比較快嗎?有些模式可就不一定喔
最好是列出程式碼來分析
如果不是需要額外的邏輯運算, 可以的話改成用 query 跑大量新增的方式
例如下列流程概念

begin tran
插入1000筆
commit

這樣就可以降低非常多的資料庫負載
------
http://blog.bestlong.idv.tw/
http://www.bestlong.idv.tw/
http://delphi-ktop.bestlong.idv.tw/
Andy Wu
一般會員


發表:17
回覆:25
積分:18
註冊:2004-02-25

發送簡訊給我
#3 引用回覆 回覆 發表時間:2010-05-19 11:15:20 IP:59.124.xxx.xxx 訂閱
事實上,我是要針對相同的資料表,其中的某一筆資料,重複不斷地 Update 這筆資料

假設,這筆資料代表的是目前陽明山上的溫度

那麼每一秒(甚至每千分之一秒)都會收到目前溫度的回報

收到資料就去更新,而且得按照收到的順序來更新

若採用您提供的
Start Trans
Update Table Set xxx = xxx Where ooo=ooo
Commit Trans
在極短時間內執行 10 萬次

效能會比直接呼叫 Stored Procedure 來的好嗎?
taishyang
站務副站長


發表:377
回覆:5485
積分:4552
註冊:2002-10-08

發送簡訊給我
#4 引用回覆 回覆 發表時間:2010-05-19 11:27:18 IP:122.116.xxx.xxx 訂閱
抱歉,小弟不懂資料庫
但小弟有個疑問,陽明山的溫度在那麼短的時間內會有劇烈變化嗎^^?


===================引 用 Andy Wu 文 章===================
事實上,我是要針對相同的資料表,其中的某一筆資料,重複不斷地 Update 這筆資料

假設,這筆資料代表的是目前陽明山上的溫度

那麼每一秒(甚至每千分之一秒)都會收到目前溫度的回報

收到資料就去更新,而且得按照收到的順序來更新

若採用您提供的
Start Trans
Update Table Set xxx = xxx Where ooo=ooo
Commit Trans
在極短時間內執行 10 萬次

效能會比直接呼叫 Stored Procedure 來的好嗎?
Andy Wu
一般會員


發表:17
回覆:25
積分:18
註冊:2004-02-25

發送簡訊給我
#5 引用回覆 回覆 發表時間:2010-05-19 11:34:31 IP:59.124.xxx.xxx 訂閱
哈哈  溫度只是舉例啦

事實上是接收<<即時性大量變化的資料>>
bestlong
站務副站長


發表:125
回覆:734
積分:506
註冊:2002-10-19

發送簡訊給我
#6 引用回覆 回覆 發表時間:2010-05-19 11:40:11 IP:60.248.xxx.xxx 未訂閱
只有在新增或更新大量資料才有改善的空間
若是大量更新同一筆資料的改善空間就很小而且方法各自不同
反而是要問你這樣的應用需求為何要在這麼短的時間更新一筆資料
你的數值精度與取樣頻率要求為何?
人眼看的清楚這樣的變化嗎?

資料的來源或許有更高的取樣頻率
但是不代表你要記錄全部的資料或是用相同的頻率去更新資料

可以的話,請再完整說明清楚你的實際需求

===================引 用 Andy Wu 文 章===================
事實上,我是要針對相同的資料表,其中的某一筆資料,重複不斷地 Update 這筆資料

假設,這筆資料代表的是目前陽明山上的溫度

那麼每一秒(甚至每千分之一秒)都會收到目前溫度的回報

收到資料就去更新,而且得按照收到的順序來更新

若採用您提供的
Start Trans
Update Table Set xxx = xxx Where ooo=ooo
Commit Trans
在極短時間內執行 10 萬次

效能會比直接呼叫 Stored Procedure 來的好嗎?
------
http://blog.bestlong.idv.tw/
http://www.bestlong.idv.tw/
http://delphi-ktop.bestlong.idv.tw/
編輯記錄
bestlong 重新編輯於 2010-05-19 11:43:33, 註解 無‧
bestlong 重新編輯於 2010-05-19 12:02:37, 註解 無‧
Andy Wu
一般會員


發表:17
回覆:25
積分:18
註冊:2004-02-25

發送簡訊給我
#7 引用回覆 回覆 發表時間:2010-05-19 12:29:17 IP:59.124.xxx.xxx 訂閱
股票與期貨行情~~

隨時都有不同的成交資料與委託資料

交易所隨時送來最新的成交資料與委託資料 必須馬上更新資料表

沒人想看五秒十秒以前的<<舊成交資料>> or <<舊委託資料>> 吧

當行情大好大壞時 交易與委託資料數量就會爆增

如果寫入 DB 的速度趕不上接收資料的速度 就會卡在自行宣告的佇列內 排隊等著寫入資料庫之中~~
bestlong
站務副站長


發表:125
回覆:734
積分:506
註冊:2002-10-19

發送簡訊給我
#8 引用回覆 回覆 發表時間:2010-05-19 15:57:34 IP:60.248.xxx.xxx 未訂閱
不知這是舉例還是您真的在這個行業, 這樣的資料源是更新大量資料
即時行情資料是由來源端PUSH出來的, 重點是不能掉資料
成交與委託資料是用戶端PULL取得的, 自行控制何時取得

人看的資料可以直接放在記憶體中處理, 沒必要存到DB後再撈出來顯示
資料源傳來的異動都去直接先更新記憶體並即時更新畫面

若需要儲存到資料庫就送去佇列排隊
然後定量或定時再批量存到DB就可以用我說的方式

===================引 用 Andy Wu 文 章===================
股票與期貨行情~~

隨時都有不同的成交資料與委託資料

交易所隨時送來最新的成交資料與委託資料 必須馬上更新資料表

沒人想看五秒十秒以前的<<舊成交資料>> or <<舊委託資料>> 吧

當行情大好大壞時 交易與委託資料數量就會爆增

如果寫入 DB 的速度趕不上接收資料的速度 就會卡在自行宣告的佇列內 排隊等著寫入資料庫之中~~
------
http://blog.bestlong.idv.tw/
http://www.bestlong.idv.tw/
http://delphi-ktop.bestlong.idv.tw/
carstyc
資深會員


發表:16
回覆:254
積分:329
註冊:2003-07-18

發送簡訊給我
#9 引用回覆 回覆 發表時間:2010-05-20 23:21:34 IP:219.84.xxx.xxx 訂閱
call store procedure ,在store procedure 裡面也是用 update 的指令。

所以就 update 的程序來講,對資料庫消耗的資源是一樣的。

而Stroe procedure 卻要多了 『呼叫Store procedure』 的系統資源,

所以在大量的呼叫Store Procedure的狀況下,Store procedure 裡面有多一點的運算,會比較划算。

若只是一個動作 update 而已,其實直接使用 update 的語法來執行,應該會比呼叫Store procedure 來的快一點。




===================引 用 Andy Wu 文 章===================
事實上,我是要針對相同的資料表,其中的某一筆資料,重複不斷地 Update 這筆資料

假設,這筆資料代表的是目前陽明山上的溫度

那麼每一秒(甚至每千分之一秒)都會收到目前溫度的回報

收到資料就去更新,而且得按照收到的順序來更新

若採用您提供的
Start Trans
Update Table Set xxx = xxx Where ooo=ooo
Commit Trans
在極短時間內執行 10 萬次

效能會比直接呼叫 Stored Procedure 來的好嗎?
carstyc
資深會員


發表:16
回覆:254
積分:329
註冊:2003-07-18

發送簡訊給我
#10 引用回覆 回覆 發表時間:2010-05-20 23:31:52 IP:219.84.xxx.xxx 訂閱
如果是股票與期貨行情,似乎用 Update 就不妥了,應該是用 Insert 比較恰當。

對同一筆Record updae的效能大概就沒啥好改善的,如果沒有 Index 就建個Index。如果本來就有Index,大概也別無他法了。

如果是 Insert 的效能,那學問就大了....

通常在Insert 的時候,資料庫會順便去對該Table的 Index 一併處理資料。所以愈多 Index 的Table, Insert 的效能愈慢。所以把Index全部刪掉,Insert時會得到最佳效能。但如果某些Index 是必需的,又不能不建這個Index時,可以考慮把 Index 及 Data 分開放在不同的DataFile中,甚至是放在不同的實體儲存硬碟中。

因為你的環境只有幾個 Thread 而已,並不是幾百個Thread ,所以應該跟 DB 同時能處理的process數量無關。

所以大概只能從資料存取的 IO 這一方面來做效能調整了。


以上僅供參考


===================引 用 Andy Wu 文 章===================
股票與期貨行情~~

隨時都有不同的成交資料與委託資料

交易所隨時送來最新的成交資料與委託資料 必須馬上更新資料表

沒人想看五秒十秒以前的<<舊成交資料>> or <<舊委託資料>> 吧

當行情大好大壞時 交易與委託資料數量就會爆增

如果寫入 DB 的速度趕不上接收資料的速度 就會卡在自行宣告的佇列內 排隊等著寫入資料庫之中~~
2007
中階會員


發表:54
回覆:90
積分:98
註冊:2008-08-12

發送簡訊給我
#11 引用回覆 回覆 發表時間:2010-05-24 09:06:50 IP:60.249.xxx.xxx 未訂閱

如果不管順序 , 10 萬筆資料大概 80 秒寫完 <--- 我覺得粉快了耶,

100,000 / 80 = 1250 筆/秒

80 / 100000 = 0.0008 秒/筆


===================引 用 Andy Wu 文 章===================
後端用 MS SQL Server 2005

前端是一個主 Thread 接收 Socket 封包資料 , 接著啟動 Multi-Thread 將資料寫回 DB 之中

寫入方式是透過 TAdoConnection TADOStoredProc , 呼叫 DB 上的 Stored Procedure 寫入至對應的 Table

同時間約有 5 個子 Thread , 各自建立各自的 TAdoConnection TADOStoredProc

呼叫寫入 DB 的函數 , 必須採用? Synchronize() , 因為寫入的順序必須等於接收的順序

因為是即時性的資料 , 所以無法收了 n 個封包後才整批寫入 , 必須一個封包就得呼叫一次 Stored Procedure

做了幾個測試的程式 , 同樣的 10 萬個封包 :

1. 1? ?個Thread 呼叫相同的 SP_1 , 共 10 萬次 , 寫入 Table_1 , 需要 100 秒
2. 50 個Thread 呼叫相同的 SP_1 , 共 10 萬次 , 寫入 Table_1 , 需要 100 秒
3. 1?? 個Thread 呼叫不同的 SP_1 , SP_2 , SP_3 , 合計總呼叫也是 10 萬次 , 寫入 Table_1 , Table_2 , Table_3 , 需要 100 秒 ( 算資料量最多的 Table )
4. 將 10 萬個封包 , 根據某條件 , 將其拆散至 3 個 TList 物件之中 , 啟動 3 個 Thread , 各自監控各自的 TList 物件 , 3 個 Thread 各自呼叫各自應該呼叫
??? 的 SP_1 , SP_2 , SP_3 , 合計總呼叫也是 10 萬次 , 寫入 Table_1 , Table_2 , Table_3 , 需要 100 秒 ( 算資料量最多的 Table )

經過以上的測試結果 , 無論 1~n 個 Thread , 感覺上 Thread 寫入 DB 的速度都是一樣的

因為我有寫入順序=接收順序 , 且為即時性的資料 , 所以寫入 DB 的方式必然會受限

如果不管順序 , 10 萬筆資料大概 80 秒寫完

想請教各位先進 , 這樣的問題是程式設計還有改進的空間 , 還是 SQL Server 有效能調校的設定呢 ?
系統時間:2017-10-18 8:05:59
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!