El 18/2/25 a les 14:49, Michael Van Canneyt via fpc-pascal ha escrit:
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.
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
--
Luca
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
