如何增加 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 有效能調校的設定呢 ? |
本站聲明 |
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。 2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。 3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇! |