On Tue, 18 Feb 2025, Luca Olivetti via fpc-pascal wrote:
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?
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.
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
