Lazarus - AutoCommit no ZeosLib com PostgreSQL

Por padrão, o PostgreSQL trata cada comando SQL como sendo executado dentro de uma transação. Ou seja, cada comando DML (INSERT, UPDATE ou DELETE) tem um comando de início de transação (BEGIN) e um comando COMMIT – caso seja bem sucedido – executados implicitamente antes e depois. Para a execução de múltiplos comandos DML isso leva a um overhead. Desta forma, sugere-se fortemente envolver todos os comandos DML em uma única transação – se a lógica da sua aplicação permitir. Para demonstrar esta recomendação fizemos um teste simples. Escrevemos um pequeno programa para inserir 5.000 linhas em uma tabela. Primeiro fizemos o teste com o controle de transação padrão - cada uma das 5.000 linhas tem seu próprio BEGIN e COMMIT - e depois modificamos o código para controlar a transação manualmente. Neste último teste todas as 5.000 linhas são inseridas dentro de uma única transação.
Para nosso teste usamos ZeosLib. Por padrão o ZeosLib também opera no modo Auto-Commit, ou seja, cada comando é executado no contexto de uma transação. Não mostraremos aqui como conectar ao PG usando Zeos, pois isso já foi apresentado no post Lazarus - Conectando PostgreSQL com ZeosLib. Vejamos o primeiro teste. A unit Dateutils deve ser incluida na cláusula uses para poder usar a função
MilliSecondsBetween(). dbBanco é um ZConnection, dmDados é um Data Module e queCidade uma ZQuery.

var
  i: integer;
  a, b: TDateTime;
begin
  dmDados.queCidade.SQL.Clear;
  dmDados.queCidade.SQL.Add('insert into cidade values (:id, :nome)');
  a := time;
  for i := 1 to 5000 do
  begin
    dmDados.queCidade.ParamByName('ID').Value:= i;
    dmDados.queCidade.ParamByName('NOME').Value:='SANTAREM';
    dmDados.queCidade.ExecSql;
  end;
  b := time;
  ShowMessage(IntToStr(MilliSecondsBetween(b,a)));
end;

Neste primeiro código o tempo médio de inserção das 5.000 linhas foi de 11,6 segundos. Alteramos então nosso código para que todas as linhas fossem inseridas dentro de uma única transação. Para isso chamamos StartTransaction antes de iniciar os INSERTs. Observe o código abaixo.

var
  i: integer;
  a, b: TDateTime;
begin
  a := time;
  dmDados.queCidade.SQL.Clear;
  dmDados.queCidade.SQL.Add('insert into cidade values (:id, :nome)');
  dmDados.dbBanco.StartTransaction;
  for i := 1 to 5000 do
  begin
    dmDados.queCidade.ParamByName('ID').Value:= i;
    dmDados.queCidade.ParamByName('NOME').Value:='SANTAREM';
    dmDados.queCidade.ExecSql;
  end;
  dmDados.dbBanco.Commit;
  b := time;
  ShowMessage(IntToStr(MilliSecondsBetween(b,a)));
end;

Neste caso o tempo médio de execução do código foi de 2,9 segundos. Um ganho de tempo considerável em relação à execução em modo padrão.
Fizemos também um teste usando TZUpdateSQL, implementado de acordo com o post citado acima. O código é apresentado em seguida e o tempo médio de execução foi de 3,8 segundos. Um desempenho um pouco pior do que o teste anterior.

var
  i: integer;
  a, b: TDateTime;
begin
  a := time;
  dmDados.queCidade.Open;
  dmDados.dbBanco.StartTransaction;
  for i := 1 to 5000 do
  begin
    dmDados.queCidade.Insert;
    dmDados.queCidade.FieldByName('ID').Value := i;
    dmDados.queCidade.FieldByName('NOME').Value:='SANTAREM';
    dmDados.queCidade.Post;;
  end;
  dmDados.dbBanco.Commit;
  b := time;
  ShowMessage(IntToStr(MilliSecondsBetween(b,a)));
end;

Estes teste foram feitos em Windows e servidor de BD e cliente rodando na mesma máquina. Certamente se você executar esses testes em outro ambiente, os tempos serão diferentes, mas comparativamente você deverá chegar à mesma conclusão.
Sabe-se que uma falha no sistema, enquanto uma transação estiver ativa, causa a execução de um ROLLBACK pelo SGBD, fazendo com que todas as atualizações feitas a partir do BEGIN sejam canceladas. Portanto, use esta recomendação com cautela e bom senso. Longas transações ativas estão sujeitas a perdas de dados muito maiores que transações curtas, em caso de falhas no sistema.

Atualizado em 28/09/2010.