Hello,

I need to copy a table between an mssql database and a postgresql one. I used a query to obtain the data, a datasource ponting to it and another query using the datasource, the sql being

INSERT INTO table (f1,f2,f3...) values (:f1,:f2,:f3,....)

and then doing

   OrigTable.Open;
   PQTrans.StartTransaction;
   while not OrigTable.Eof do
   begin
     DestTable.ExecSql;
     OrigTable.Next;
   end;
   PQTrans.Commit;
   OrigTable.Close;

this works but it's too slow (it takes about 700ms for each ExecSql), because it's constantly preparing and unpreparing the statement.

If I manually prepare an insert statement for each original record and use it with ExecuteDirect it only takes 30ms, considering that I access the server through a vpn I think it's normal.

I'm quite sure it's a PEBKAC, so, is there a way to reduce the time for the ExecSql?

Bye

You must prepare the desttable query before starting the loop. Then it does not need to prepare/unprepare.

the faster way is using a foreign table definition in Postgres, though. You can then do the copy with 1 statement.

Michael.

El 18/2/25 a les 14:49, Michael Van Canneyt via fpc-pascal ha escrit:

Silly me, I knew it was a PEBKAC ;-) Thank you.

   OrigTable.Open;
   PQTrans.StartTransaction;
   DestTable.Prepare; // <------- that's it
   while not OrigTable.Eof do
   begin
     DestTable.ExecSql;
     OrigTable.Next;
   end;
   DestTable.UnPrepare;
   PQTrans.Commit;
   OrigTable.Close;

still a bit slower than manually constructing the query (47ms vs 30ms) but it works.

the faster way is using a foreign table definition in Postgres, though. You can then do the copy with 1 statement.

I know, but I don't want to go that route (at least not now).

Bye