如何增加 MS SQL Server的寫入速度 |
|
答題得分者是：carstyc
|
|
Andy Wu
一般會員   發表：17 回覆：25 積分：18 註冊：2004-02-25 發送簡訊給我 |
後端用 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
站務副站長   發表：126 回覆：734 積分：512 註冊：2002-10-19 發送簡訊給我 |
我對 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 發送簡訊給我 |
|
|
taishyang
站務副站長  發表：377 回覆：5490 積分：4563 註冊：2002-10-08 發送簡訊給我 |
抱歉,小弟不懂資料庫
但小弟有個疑問,陽明山的溫度在那麼短的時間內會有劇烈變化嗎^^? ===================引 用 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 發送簡訊給我 |
|
|
bestlong
站務副站長 發表：126 回覆：734 積分：512 註冊：2002-10-19 發送簡訊給我 |
只有在新增或更新大量資料才有改善的空間
若是大量更新同一筆資料的改善空間就很小而且方法各自不同 反而是要問你這樣的應用需求為何要在這麼短的時間更新一筆資料 你的數值精度與取樣頻率要求為何？ 人眼看的清楚這樣的變化嗎？ 資料的來源或許有更高的取樣頻率 但是不代表你要記錄全部的資料或是用相同的頻率去更新資料 可以的話,請再完整說明清楚你的實際需求 ===================引 用 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/ |
|
Andy Wu
一般會員 發表：17 回覆：25 積分：18 註冊：2004-02-25 發送簡訊給我 |
|
|
bestlong
站務副站長 發表：126 回覆：734 積分：512 註冊：2002-10-19 發送簡訊給我 |
不知這是舉例還是您真的在這個行業, 這樣的資料源是更新大量資料
即時行情資料是由來源端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 發送簡訊給我 |
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 發送簡訊給我 |
如果是股票與期貨行情，似乎用 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 發送簡訊給我 |
如果不管順序 , 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 有效能調校的設定呢 ? |
發表時間：2010-05-14 00:10:13
IP:220.134.xxx.xxx 訂閱
系統時間：2024-04-26 11:05:48
聯絡我們 | Delphi K.Top討論版
| 本站聲明 |
|
1. 本論壇為無營利行為之開放平台，所有文章都是由網友自行張貼，如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權，而損及您的利益，請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨，是藍是綠本站無權干涉，但這裡不是政治性論壇！ |