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

[Michael Van Canneyt]

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.