我想請問一下以下兩種寫法有什麼差別 寫法一: Query->Close(); Query->SQL->Clear(); Query->SQL->Add("SELECT * FROM TestTable WHERE Field1>100 and Field2=5"); Query->Open(); 寫法二: Query->Close(); Query->SQL->Clear(); Query->SQL->Add("SELECT * FROM TestTable WHERE Field1>:V1 and Field2=:V2"); Query->ParamByName("V1")->AsInteger=100; Query->ParamByName("V2")->AsInteger=5; Query->Prepare(); Query->Open(); 有人說寫法二會比較快,為什麼呢

davidchang, 您好 我覺得都一樣耶！ 您說寫法二比較快，有無相關的數據比較呢？    ================================= 涵養怒中氣。謹防順口言。留心忙裡錯。珍惜有時錢。 是非終日有，不聽自然無 天下本無事，庸人自擾之

因為我也覺的都一樣 但客戶的DBA硬要我們改成方法二的方式,他說會比較快 客戶是老大,所以我也只好照著改 只是一直很懷疑才上來問問 若真的比較快,DBA怎會知道你要用這種方法呢,他又不會BCB之類的 實作不是Compiler做掉的嗎

事實上是不一樣耶! 你連上Database後用Profiler監看,就會發現

HI, 就這兩種來看, 確實是一樣快的沒錯, 但是若是這樣子來看就不一樣了.    第一種:

for i = 1 to 10 do
begin
  Query->Close();
  Query->SQL->Clear();
  Query->SQL->Add("SELECT * FROM TestTable WHERE Field1>100 and Field2=" IntToStr(i));
  Query->Open();
end;

================================= 第二種:

Query->SQL->Clear();
Query->SQL->Add("SELECT * FROM TestTable WHERE Field1>:V1 and Field2=:V2");
Query->Prepare();    for i=1 to 10 do
begin
  Query->Close();
  Query->ParamByName("V1")->AsInteger=100;
  Query->ParamByName("V2")->AsInteger=i;
  Query->Open();
end;

============================ 原因如下(引敍help說明) Description Call Prepare to have the BDE and a remote database server allocate resources for the query and to perform additional optimizations. Preparing a query consumes some database resources, so it is good practice to call UnPrepare once the query is no longer needed. If the query will only be executed once, the application does not need to explicitly call Prepare or UnPrepare. Executing an unprepared query generates these calls automatically. However, if the same query is to be executed repeatedly, it is more efficient to prevent these automatic calls by calling Prepare and UnPrepare explicitly. Note: When you change the text of a query at runtime, the query is automatically closed and unprepared. 發表人 - timhuang 於 2003/10/31 16:54:04

所以結綸是若以我之前提的兩個方法來做, 效能是差不多的 若是同一段SQL語法要重覆使用多次, 只有條件子句不太相同時(也就是timhuang大大的測試方法), 效能就會差很多 ps:我也依照jcjroc大大提供的方法去執行SQL profiler 最後交付的SQL語法是不一樣, 但還是看不出效果差在那 又試著在二十幾萬筆資料中, 測試兩種方法的執行時間, 也都是差不多的 經由timhung大大的解釋, 才知是我測試的方法錯誤 總而言之,感謝timhung及jcjroc大大的指導