各位先進們好， 因為最近在研究使用Delphi連結各類資料庫更新資料的方法， 有些關於更新資料庫的效能問題想請先進們指點一下。 ◎第一階段： 當初剛開始設計程式時考慮到為了不想讓Server處理多次交易 所以先使用TAdoQuery將想要更新的資料一次抓取回Client端 並用TClientDataSet連結到TAdoQuery, 使用TClientDataSet的Filter功能 配合我所需要的新增(TClientDataSet.Append), 刪除(TClientDataSet.Delete), 與更改 每筆資料編輯過之後先使用TClientDataSet.Post存到Client端 最後每完成一個資料表就使用TClientDataSet.AllpyUpdate將Client端資料上傳至Server端 這樣是打算可以讓Server對一個使用者更新一個資料表只需要處理一次交易 可是發現當一次對一個資料表新增3000筆資料時, ApplyUpdate的時間會很漫長 約5分鐘左右 ◎第二階段： 後來就嘗試直接使用TAdoQuery的SQL來新增資料, 將每一筆需要新增的資料改寫成SQL語法填入TAdoQuery內, 並執行TAdoQuery.ExecSQL 這樣會變成每一個使用者每新增一筆資料, Server端就必須處理一筆交易 不過如此新增3000筆資料到Server端卻僅需要10秒左右的時間 以上提到的是新增資料的部份(Insert, Append), 而Update的部份似乎又剛好相反 因為我並沒有去細分單一筆資料的欄位是否有不同才決定要不要Update 而是只要Primary Key欄位相同就會去Update其他不是Primary Key的欄位 而使用TAdoQuery.SQL的Update...Set...的時間反而比TClientDataSet的Update方式更久 因為我測試時是以相同資料重覆測試, 所以我猜想可能是TClientDataSet有過濾的功能 在ApplyUpdate時會去比較更改前後的資料, 如果資料相同就不丟回Server端進行更新的動作 只是不知道原理是不是真的是這樣才導致時間比直接使用TAdoQuery.SQL的要短 可是兩者的Update時間仍然不理想, 3000筆資料約2,30秒 至於刪除資料並沒有很認真的測過 不過應該不需要太多時間 ◎第三階段： 因為Update時間不是很理想, 所以我又改了一下作法 進入資料表時, 先將相關資料使用TAdoQuery一口氣Delete掉(Delete From ... Where ...) 然後僅使用TAdoQuery新增資料, 這樣的時間倒還是可以接受 附註：此處因為都是我自己電腦的測試過程， 所以此處所提的Server其實是我自己電腦的Oracle,MS-SQL資料庫 所以秒數可能不是很準，不過時間長短的比較應該可作參考 在這個過程中，有些對於Delphi原件與資料庫的疑問想請教一下各位先進： １、關於之前提到TClientDataSet對於相同資料不丟回Server端要求更新而顯得 速度比直接使用SQL語法Update要快的假設是否真的如此？ ２、3000筆資料使用TClientDataSet.ApplyUpdate僅跟Server進行一次交易與使 使用TAdoQuery.ExecSQL進行3000次交易，會對Server造成旁大的負擔嗎？ 如果有多個使用者同時使用此功能，影響Server會更鉅大嗎？ ３、第三階段的作法對目前來說是最快的作法，可是除了第２點的疑慮外，如果 資料庫已經使用一段時間，說不定已經有數百萬筆的資料量了，如果還是使 用先刪再新增不更新的方法，會不會大量的降低原有的效能呢？ ４、因為我目前只針對Oracle與MS-SQL做測試，而最終目標是透過TAdoConnection、 TAdoQuery、TClientDataSet等原件來連結到各資料庫，以最小的改變，最大的 效率來更新不同種類資料庫的相同資料表欄位，而照目前的作法是否可以應用在 其他的資料庫呢？如：Sybase、FoxPro、Infomix等等

請參考以下事項：    1. 是否使用了Transaction？    新增/更新整批資料時，若不使用 Transaction 將導致每一個 SQL 命令都會做 Commit 動作，這樣會大大降低效能。建議使用 Transaction，且每固定筆數(例如 1000 筆) Commit 一次    2. ClientDataSet在更新資料時是很聰明的，如果把一筆資料 Edit 且 Post，但是所有欄位都沒有變動的話，這一筆在 ApplyUpdates 時實際上不會做更新的動作。如果 Insert / Append 一筆之後，再 Edit 的話，在 ApplyUpdates 時只會以最新資料做新增的動作，不會先新增再修改。    所以關於你提出的疑問，我個人的看法是： 1. 是的    2. 交易次數越多，Server 負擔越大，User 多的話更慘。 不過如果使用 TADOConnection 的BeginTrans、CommitTrans、RollbackTrans 等三個 method 來做交易控管的話，用 ClientDataSet 跟用 ADOQuery 是沒兩樣的。典型的交易控制程式段如下例：

Conn.BeginTrans;
try
  // 以 ClientDataSet 做資料更新
  cds.ApplyUpdates;      // 或以 ADOQuery 做資料更新
  for i := 1 to aCount do
  begin
    ....
    aq.ExecSQL;
  end;      Conn.CommitTrans;
except
  on E: Exception do
  begin
    Conn.RollbackTrans;
    ShowMessage('更新失敗！原因為：'#10   E.Message);    // #10 = 換行字元
  end;
end;

3. 如果資料庫的統計資料夠新的話，還是以更新為好，因為先刪除再新增是兩個 SQL 命令，而更新只是一個 SQL 命令。而且更新跟刪除一樣要透過索引找到要更新/刪除的資料，搜尋到該筆資料的時間是一樣多的。所以當資料筆數很大時要考慮的應該是如何有效的維持索引和統計資料，還有要考慮是不是要使用歷史 Table 把太舊的資料歸檔。 4. 依照你這樣的作法，搭配上交易控制的話，可以用於各種資料庫。 加油喔，喵～

------
歡迎參訪 "腦殘賤貓的備忘錄" http://maolaoda.blogspot.com/

感謝sryang前輩的回答，所以答題得分者我將選擇sryang前輩 不過由於還有些問題想再討論一下，所以暫不結案 如果有前輩有些其他的想法，希望可以不吝賜教    關於第一、四個問題解答已經很清楚， 也大大地加強了我繼續寫這個程式的信心。    關於想再討論一下的問題我會用橘色標示出來 其他的部份都是屬於說明，希望各位前輩能再給予指教 -------重點ㄧ----------- 至於第二個問題，可能我發問的語法不太對 首先先感謝sryang前輩的提醒，不過我這部份忘了提 我已經有使用AdoConnection的BeginTrans、CommitTrans跟RollBackTrans 而且是一開始就BeginTrans最後才CommitTrans或RollBackTrans 也就是中間將更新不定數量的資料表 不過後來發現時間使用最多的是ClientDataSet.ApplyUpdates或是 AdoQuery.ExecSQL 雖然兩者都是包含在BeginTrans與CommitTrans中間 可是更新資料表的速度倒是差很多 就如原主題內所提到的，大概是五分鐘與十秒鐘的速度 我為了使用者當然是選擇時間較少的AdoQuery.ExecSQL 不過因為擔心速度快並不表示資料庫能負擔 所以才有第二個問題產生 我這邊把第二個問題再重組一次 同樣的3000筆資料要新增(或更新)到一個資料表中 有以下兩個方法 A. 使用ClientDataSet.Append並填值，三千次，然後使用ApplyUpdates(約耗時5分) B. 使用Insert語法並執行AdoQuery.ExecSQL，三千次(約耗時10秒) 問題是： 1. 請問何種方法會對Server造成較龐大的負擔? 還是兩者對Server的負擔相同? 2. 如果有五個人同時做同一個動作，只是資料內容不同，資料量假設相同， 以上兩者的表現是否會大幅降低，如一個人新增完成的時間由5分變25分 由10秒變成50秒，還是維持原有的表現? (假設上傳到同一台Server的同一個資料表，網路速度相同，電腦設備相同) 希望各位前輩能再給予指教 -------重點二----------- 另外關於sryang前輩所回答的第三個問題 因為同樣內容的三千筆資料，我所測試的上傳時間結果是 Append 3000次的ApplyUpdate>更新三千筆相同資料的ApplyUpdate> Update 3000次相同資料的ExecSQL>Insert 3000次的ExecSQL 雖然sryang前輩說過，所有欄位都沒有變動的話，ApplyUpdates實際上不會做更新的動作 不過時間上還是遠比Update資料的ExecSQL久 而Update資料的ExecSQL又比Insert資料的ExecSQL還久一點 所以我才會有使用Delete Insert取代Update的寫法 完全是因為使用者注重的是臨場的速度表現， 對於是否會對資料庫造成龐大的負擔則置之不理 因為我沒有這樣的設備與資料量來測試假以時日後， 使用者有多人同時上傳已經擁有數百萬筆資料的資料庫時，整個程式是否還有現在的表現 所以想借助各位前輩的經驗，研究一下， ApplyUpdates與ExecSQL的速度是否會隨著資料庫的日漸龐大而越來越慢? 還是希望各位前輩能再給予指教 至於把太舊的資料歸檔可能無解，因為舊資料也可能在假以時日之後再被查詢 如果真的要備份，應該也是由使用者自行備份並從資料庫內移除 我們程式設計者考慮到以舊資料可能會再被拿出來用的前提 不太適合由程式自動幫使用者備份資料並移除

ClientDataSet 在 ApplyUpdates 時，DatasetProvider 會因為 ResolveToDataSet 屬性的不同，而把實際的 update 的動作由自己組 SQL 來做或是由 DataSet 屬性所指的 DataSet 來做。 若 DataSet 使用 TADODataSet 的場合，建議把 ResolveToDataSet 設定為 True，也就是由 ADO 來做 update 的動作 若 DataSet 使用 TQuery 的場合，ResolveToDataSet 必須設定為 False，否則無法完成 update 的動作。因為用 TQuery 的話，資料集是唯讀的，沒辦法更新資料庫 還有，ClientDataSet 裡面所包含的 TField 的 ProviderFlag 屬性的設定，預設值是 [pfInUpdate, pfInWhere]，應該依照實際的需要，將 key 值欄位的 pfInWhere 設定為 True，其他欄位的 pfInWhere 設定為 False。否則 ApplyUpdates 時，變成所有的欄位都在 where 子句中，想要快也難啊。 所以你應該試試 1. DatasetProvider.ResolveToDataSet 設定為 True 或是 2. 依照實際狀況調整 ClientDataSet 裡面所包含的 TField 的 ProviderFlag 屬性。若 DatasetProvider.ResolveToDataSet 設定為 True 時，調整這個是無效的喔～～ 另外，做這些調整的同時，開啟資料庫的監視程式，來觀察程式下給資料庫的 SQL 語句 做完這些測試和觀察之後，相信你應該可以得到答案的 加油喔，喵～

------
歡迎參訪 "腦殘賤貓的備忘錄" http://maolaoda.blogspot.com/