Delphi FireDAC SQLite 批量提交 SQL 命令測試（十一）：TFDConnection.ExecSQL 與 TFDQuery 執行效能對比

前言

FireDAC 執行大量 SQL（如批次 INSERT、Schema 更新）時，TFDConnection.ExecSQL、TFDQuery.ExecSQL、TFDCommand、底層 TSQLiteStatement 效能差異顯著。本文提供可直接貼上表單的完整測試專案，對比各種批量執行方式的耗時。

表單元件配置（複製以下文本至 .dfm 或手動拖放）

object DBGrid1: TDBGrid
  Left = 0; Top = 0; Width = 265; Height = 338; Align = alLeft
  DataSource = DataSource1; TabOrder = 0
end
object Button1: TButton; Left = 280; Top = 24; Width = 75; Height = 25; Caption = 'ExecSQL Array'; TabOrder = 1; OnClick = Button1Click; end
object Button2: TButton; Left = 280; Top = 64; Width = 75; Height = 25; Caption = 'Query ExecSQL'; TabOrder = 2; OnClick = Button2Click; end
object Button3: TButton; Left = 280; Top = 104; Width = 75; Height = 25; Caption = 'Command'; TabOrder = 3; OnClick = Button3Click; end
object Button4: TButton; Left = 280; Top = 144; Width = 75; Height = 25; Caption = 'TSQLiteStatement'; TabOrder = 4; OnClick = Button4Click; end
object FDConnection1: TFDConnection; Left = 66; Top = 48; end
object FDPhysSQLiteDriverLink1: TFDPhysSQLiteDriverLink; Left = 167; Top = 48; end
object FDGUIxWaitCursor1: TFDGUIxWaitCursor; Provider = 'Forms'; Left = 164; Top = 120; end
object FDQuery1: TFDQuery; Connection = FDConnection1; Left = 56; Top = 192; end
object DataSource1: TDataSource; DataSet = FDQuery1; Left = 60; Top = 120; end

測試代碼邏輯

uses System.Diagnostics, FireDAC.Phys.SQLiteWrapper;

const TEST_COUNT = 50000;

procedure TForm1.Button1Click(Sender: TObject); // FDConnection.ExecSQL 陣列參數
var SW: TStopwatch; I: Integer;
begin
  FDConnection1.ExecSQL('CREATE TABLE TestBatch (ID INTEGER PRIMARY KEY, Val TEXT)');
  SW := TStopwatch.StartNew;
  FDConnection1.StartTransaction;
  try
    for I := 1 to TEST_COUNT do
      FDConnection1.ExecSQL('INSERT INTO TestBatch (Val) VALUES (:v)', ['Item ' + I.ToString]);
    FDConnection1.Commit;
  except FDConnection1.Rollback; raise; end;
  SW.Stop; ShowMessage(Format('FDConnection.ExecSQL: %d ms', [SW.ElapsedMilliseconds]));
end;

procedure TForm1.Button2Click(Sender: TObject); // TFDQuery.ExecSQL
var SW: TStopwatch; I: Integer;
begin
  FDConnection1.ExecSQL('CREATE TABLE TestBatch2 (ID INTEGER PRIMARY KEY, Val TEXT)');
  SW := TStopwatch.StartNew;
  FDConnection1.StartTransaction;
  try
    FDQuery1.Connection := FDConnection1;
    for I := 1 to TEST_COUNT do
      FDQuery1.ExecSQL('INSERT INTO TestBatch2 (Val) VALUES (:v)', ['Item ' + I.ToString]);
    FDConnection1.Commit;
  except FDConnection1.Rollback; raise; end;
  SW.Stop; ShowMessage(Format('TFDQuery.ExecSQL: %d ms', [SW.ElapsedMilliseconds]));
end;

procedure TForm1.Button3Click(Sender: TObject); // TFDCommand
var SW: TStopwatch; I: Integer; Cmd: TFDCommand;
begin
  FDConnection1.ExecSQL('CREATE TABLE TestBatch3 (ID INTEGER PRIMARY KEY, Val TEXT)');
  Cmd := TFDCommand.Create(nil);
  try
    Cmd.Connection := FDConnection1;
    Cmd.CommandText.Text := 'INSERT INTO TestBatch3 (Val) VALUES (:v)';
    SW := TStopwatch.StartNew;
    FDConnection1.StartTransaction;
    try
      for I := 1 to TEST_COUNT do begin
        Cmd.ParamByName('v').AsString := 'Item ' + I.ToString;
        Cmd.Execute;
      end;
      FDConnection1.Commit;
    except FDConnection1.Rollback; raise; end;
    SW.Stop; ShowMessage(Format('TFDCommand: %d ms', [SW.ElapsedMilliseconds]));
  finally Cmd.Free; end;
end;

procedure TForm1.Button4Click(Sender: TObject); // TSQLiteStatement 底層 API
var SW: TStopwatch; I: Integer; Stmt: TSQLiteStatement;
begin
  FDConnection1.ExecSQL('CREATE TABLE TestBatch4 (ID INTEGER PRIMARY KEY, Val TEXT)');
  Stmt := TSQLiteStatement.Create(FDConnection1.CliObj);
  try
    Stmt.Prepare('INSERT INTO TestBatch4 (Val) VALUES (?)');
    SW := TStopwatch.StartNew;
    FDConnection1.StartTransaction;
    try
      for I := 1 to TEST_COUNT do begin
        Stmt.BindText(1, 'Item ' + I.ToString);
        Stmt.Execute;
        Stmt.Reset;
      end;
      FDConnection1.Commit;
    except FDConnection1.Rollback; raise; end;
    SW.Stop; ShowMessage(Format('TSQLiteStatement: %d ms', [SW.ElapsedMilliseconds]));
  finally Stmt.Free; end;
end;

預期效能排序（由快到慢）

1. TSQLiteStatement：直接呼叫 SQLite C API，零封裝開銷。
2. TFDCommand：輕量指令物件，參數綁定高效。
3. FDConnection.ExecSQL：內部建立臨時命令，有微小開銷。
4. TFDQuery.ExecSQL：查詢物件功能最豐富但最重，批量寫入最慢。

優化建議

• 大量寫入**務必使用交易** (StartTransaction/Commit)，否則每條 SQL 獨立提交極慢。
• 啟用 PRAGMA journal_mode = WAL 並發讀寫不阻塞。
• 參數化 SQL 避免重複解析，FireDAC 會快取 Prepared Statement。
• 極大批次（>100萬）考慮 COPY 或 CSV 匯入工具。

結語

批量 SQL 執行效能核心在於「交易 + 參數化 + 輕量 API」。FireDAC 封裝層級越薄越快，TSQLiteStatement 直達底層是極致選擇，TFDCommand 則是開發效能與執行效能的最佳平衡點。