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

[Luca Olivetti]

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