[fpc-pascal] Re: Firebird: bulk insert performance: suggestions?

[Reinier Olislagers]

On 7-9-2012 13:12, michael.vancanneyt-0Is9KJ9Sb0A at public.gmane.org wrote:
> On Fri, 7 Sep 2012, Reinier Olislagers wrote:
>> then the transaction is started (if it is inactive) and the query
>> parameters are filled (using Query.Params.ParamByName, but I don't
>> suppose that would be a big slowdown??); finally the SQL is executed.
>> The transaction is left open.
> 
> Do you prepare the query before you start the batch ?
> If not, it is prepared on every insert, which is inherently slower.
I didn't do an explicit .Prepare, but I've added it, thanks.
I thought sqldb would prepare automatically if you are using
parameters though?

>> Currently, after every 100 records, the transaction is committed:
>> if (linenum mod 100=0) then
>>  FDBLayer.BulkInsertCommit(false);
>> IIRC, advice on the Firebird list is to play with this interval; any
>> suggestions? Given the aggressive nature of the transaction parameters,
>> I might even dispense with it.
> 
> I once did tests with that (600.000 records) and did not notice any
> influence
> of the transaction control.
Ok, thanks.

Time to add some timing output to the GUI ;)
(Though my stored procedure could probably be optimized as well, I
suppose... perhaps I'll try on the Firebird list)