[fpc-pascal] sqldb, copy table between databases is painfully slow

[Luca Olivetti]

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
-- 
Luca