日前做了一些實驗,不過跟我看書或是從網路上得到的訊息有點不太一樣.. 所以想請教一下各位先進,是否我的做法有誤??    我 insert  700 * 25 = 17500 筆資料 .. 利用三種方式做實驗,以下元件皆未接感知元件,且無關聯表格 :    1. TADOQuery 使用 SQL Command 類似 "INSERT MyTable (c1,c2) VALUES (:c1,:c2)" 的方式,加上 Prepare := true; 以迴圈的方式跑: 

for j:=1 to 25 do
for i:=1 to 700 do
begin
  adoquery.Parameters.ParamByName('c1').Value := C1Value;
  adoquery.Parameters.ParamByName('c2').Value := C2Value;
  adoquery.ExecSQL;
end; 

2. TADOStoredProc 加上 Prepare := true : MS SQL 之 Stored Procedure : CREATE PROCEDURE [dbo].[MapDieDataInsert] ( @TestResult AS c1, @AppOfMarking AS c2, ) AS INSERT [TRD].[MapDieData] (c1,c2) VALUES (@c1,@c2) GO Delphi :

for j:=1 to 25 do
for i:=1 to 700 do
begin
  adosp.Parameters.ParamByName('c1').Value := C1Value;
  adosp.Parameters.ParamByName('c2').Value := C2Value;
  adosp.ExecProc;
end; 

3. 改用 dbExpress 連接,使用方法同 2. 結果發現方法 1 耗時 92 sec ,方法 2 耗時 98 sec, 方法 3 只需 75 sec .. 當然 GetTickCount 出來的秒數只能做一個相對參考值.. 不過,照我看到網路討論的文章或是一般書籍所述,應該使用 Store Procedure 的速度應該會快很多才對,怎會比利用 ADOQuery 執行 Insert 的速度還要慢?? (dbExpress 的表現倒是很令人驚豔) 是否我使用 Store Procedure 的程序有錯?? 另外再請教, ADO 元件對於要 Insert 大量資料,有沒有可以微調的參數,或是特殊的做法可以加速 insert 的速度?? ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ---- 發表人 - SouthWind 於 2004/05/14 15:33:18

你可以    1. 參考 BULK INSERT 指令用法 2. 參考命令列指令 BCP 的用法 3. 使用 DTS 匯出匯入工具    其中 1, 2 速度較快, 3 的話, 可以做更彈性的功能.    至於你問到的 stored procedure 的問題, 基本上還是利用外界傳入資料的方式來寫入資料庫, 所以效能並不會比較快, 而且有可能更慢是可以理解的, 因為原來用指令一行就輸入完成了, 但使用了一筆一筆的 stored procedure 來寫入, 要先找到該 sp 再執行, 看起來就比較慢沒錯, 至於 dbexpress 的話, 弟比較沒有研究, 還請有經驗的前輩協助答覆.    你要測試出 stored procedure insert 比較快的方式應該這麼做,

CREATE PROCEDURE [dbo].[MapDieDataInsert] 
  @c1 varchar(50),
  @c2 varchar(50)
AS    declare @i, @j
select @i=1, @j=1
while @i<=25 
begin
  while @j<=700
  begin
    INSERT [TRD].[MapDieData] (c1,c2) VALUES (@c1,@c2)
    select @j = @j ＋ 1
  end
  select @i = @i ＋ 1
end
GO

這樣就能看得出來差異囉!!

引言: 你可以 1. 參考 BULK INSERT 指令用法 2. 參考命令列指令 BCP 的用法 3. 使用 DTS 匯出匯入工具 其中 1, 2 速度較快, 3 的話, 可以做更彈性的功能. 至於你問到的 stored procedure 的問題, 基本上還是利用外界傳入資料的方式來寫入資料庫, 所以效能並不會比較快, 而且有可能更慢是可以理解的, 因為原來用指令一行就輸入完成了, 但使用了一筆一筆的 stored procedure 來寫入, 要先找到該 sp 再執行, 看起來就比較慢沒錯, 至於 dbexpress 的話, 弟比較沒有研究, 還請有經驗的前輩協助答覆. 你要測試出 stored procedure insert 比較快的方式應該這麼做,

CREATE PROCEDURE [dbo].[MapDieDataInsert] 
  @c1 varchar(50),
  @c2 varchar(50)
AS    declare @i, @j
select @i=1, @j=1
while @i<=25 
begin
  while @j<=700
  begin
    INSERT [TRD].[MapDieData] (c1,c2) VALUES (@c1,@c2)
    select @j = @j ＋ 1
  end
  select @i = @i ＋ 1
end
GO

這樣就能看得出來差異囉!! < face="Verdana, Arial, Helvetica"> timhuang Sir 您好 : 感謝您的解釋,我已經理解為何用 Store Procedure 會比較慢了.. 我的疑問是來自李維先生在"高效率資料程式設計"一書中最後關於新增大量資料的討論, P11-5 有一段話 : "當然,我們可以修改上面的程式,讓它直接使用 TSQLDataSet 元件,以 SQL 敘述直接新增資料,這樣的執行速度又會快上許多,不過使用 TSQLDataSet 元件仍然比不上使用預儲程序來處理大量的資料,因為我們可以利用資料庫快儲預備程序的特性讓執行的速度更為迅速" 且書中提供了一個,數據 6.739 sec 可以 Insert 10000 筆資料.. 跟我的比起來實在差太多了,光碟內也沒有關於這段 Stroe Procedure 的描述,所以我很好奇,這是怎樣達成的?? 從您的 Store Procedure 看起來會呼叫一次會產生 700 * 25 的 "相同" 資料,我需要 insert 大量的不同資料,不知道是否是我誤解您的 Store Procedure Code?? (剛學 T-SQL ,很多不懂,抱歉..) 還是 timhuang Sir 只是透過這個例子與一筆筆 insert 的 Store Procedure 做個對照比較?? 關於您提供的三個方法,似乎都是從檔案內做大量搬移資料的動作(以我粗淺的理解),我是希望能試出動態 (in-Memory Data) insert ,我接下來要遇到 Case,是從一 Binary File 解析出大量資料 (一次大約是 3~6 萬筆上下),由於是特殊格式,讀出來要先轉換,然後再一筆一筆 insert 進入資料庫.. ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ---- 發表人 - SouthWind 於 2004/05/14 18:44:07

基本上, 我寫的 sp 確實是都塞入一樣的資料沒錯, 但目的在於告訴你 sp 的高效能僅能在資料庫內發揮, 例如參考某些 table 進行複雜的運算後再寫入某些 table, 若是使用外部程式來進行的話, 效能當然會得多囉! 另外關於你提到有關大量資料寫入資料庫, 並非是完整的檔案型態, 而是某種格式的二進位檔, 這樣的就, 弟所提及的三種工具皆無法完成你的需求, 因為必須先進行 parse 後再寫入資料庫. 方式分為兩種, 一為一邊 parse, 一邊 insert, 這樣的話, 該檔案 parse 完成, 也就寫入完成了, 但不知會不會有 transaction 的問題, 也就是寫入檢查或一次性寫入的問題, 若沒有的話, 這個方式就可以了, 但若會發生上面的問題時, 弟的建議是你可以寫一支程式 parse 二進位檔為 csv 檔, 或是 bcp / bulk insert 可以接受的文字檔, 然後再利用工具做一次性寫入, 不僅可以在寫入前先確認資料是否有誤, 也可以進行寫入資料庫的一次性管理!

您好：  

 Var
  SQLStr: String;
  i:integer;
begin
  SQLStr:= '';
  for i:= 1 to 10000 do
    SQLStr:= SQLStr 'insert TABLE1 values(' inttostr(i) ');';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(SQLStr);
  ADOQuery1.ExecSQL;

這樣10000筆資料只需要8秒多，時間長的原因通常都是跟資料庫連結的次數多，只要一次將SQL語法串好，然後丟給資料庫執行就會快很多 ~~應無所住而生其心~~ 發表人 - terrychen 於 2004/05/16 08:56:03

引言: 您好：

 Var
  SQLStr: String;
  i:integer;
begin
  SQLStr:= '';
  for i:= 1 to 10000 do
    SQLStr:= SQLStr 'insert TABLE1 values(' inttostr(i) ');';
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(SQLStr);
  ADOQuery1.ExecSQL;

這樣10000筆資料只需要8秒多，時間長的原因通常都是跟資料庫連結的次數多，只要一次將SQL語法串好，然後丟給資料庫執行就會快很多 ~~應無所住而生其心~~ 發表人 - terrychen 於 2004/05/16 08:56:03

terrychen 您好 : 我所用的方法一,應該會比你的快一點 (或相同),決定的因素應該在 prepare 這個 option 有沒有打開,從 M$ SQL profilter Tool 可以看出 端倪(M$ SQL 可以一次傳送多筆 SQL Command 嗎? 如果可以的話, Remote 端呼叫 DataBase 就可以提升效率了) 我昨天在 Local 端執行了我原來的實驗,發現方法一只需要 16 sec .. 所以可以證實網路 round-trip 造成的影響比我想像的要大許多 ... (Log 顯示 RPC 連接花了不少時間).. 如果扣掉 dbExpress 省下的 Connection 時間與取得 MetaData 的時間,就跟李維先生的差不多了... 如果仔細觀察 profilter ,事實上方法一經過 prepare := true 後, M$ SQL 跑的是一個系統的 sp ... 我現在還在實驗 timhuang Sir 的利用 cvs BULK Insert 方式,看效率有沒有大量提升?? 有結果再分享一下心得... ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ---- 發表人 - SouthWind 於 2004/05/16 13:26:09 發表人 - SouthWind 於 2004/05/16 13:45:56

綜合整理一下這幾天的實驗心得 (ADO 元件 MS SQL 2000): 發表於 http://delphi.ktop.com.tw/topic.php?TOPIC_ID=50301 timhuang Sir : 我實驗了一下,扣除網路 Round-Trip 的 overhead,但要增加寫入 file 的 overhead,實際上因為資料數未達到一定的量 (我相信在超大量的狀況會有差異),所以這種方法在我的 case 上跑起來比較慢... ---- 我只會兩件事,這也不會,那也不會 眼見不一定為真 ----