Aid with update performance

Question

I have the following situation: I have to update some ids with the where in a requisition number, but are between two banks, firebirds, different. I did this:

while not qry1.eof do
begin
  Qry2.sql.text := ' update tabela set id = 
  qry1.fieldbyname('id').asstring where numero = 
  qry1.fieldbyname('numero').asstring ' ;

  Qry2.execsql;

  Qry1.next;
End;

This works (I wrote a little different here not to write a lot). But this way it takes longer than I would like.

Does anyone have any idea how to improve this?

Answer 1

Create a list of updates and run them at once, something like:

EXECUTE BLOCK AS
BEGIN
  UPDATE tabela WHERE ID = X;
  UPDATE tabela WHERE ID = X;
  UPDATE tabela WHERE ID = X;
  ...
END;

Very easy and fast. You can use the same loop as you are doing, but instead of adding it to the query and running it, add it to a list and then run the whole block.