Subject Re: [IBO] Very slow execution if Transaction.AutoCommit is true
Author patrick_marten

> First, sorry for not really answering your question...

No problem, even without a direct answer a reply can be helpful :)

> Have you looked at database statistics before, during and after your import, particularly the size of the gap between oldest (active) transaction and next transaction? I think AutoCommit defaults to CommitRetaining (AutoCommitRetains is false by default) and until a hard commit is done, that could stop the OAT from advancing.

No, I didn't take a look at database statistics. Until recently I somehow missed the whole statistics part and never used it... There is still a lot to learn etc.

> I doubt it matters, but why do you use tiConcurrency and not tiCommitted? Is there any reason for having a static view of the data when you import (the static view that tiConcurrency provides is good for export or reports, but you are importing)?

Hm, no, there is no certain reason to use tiConcurrency instead of tiCommitted. There are several update steps. Import and the routine to fill in missing data are only two of these steps, but as long as everything happens within the same transaction, there shouldn't be a problem and tiCommitted should work too. But as you said, it seems not to matter (at least in this case). Still thanks for the hint, sometimes things are getting overlooked...

> Committing all the time is time consuming (as you've already experienced). But having separate update statements for each update also makes things far slower than they need to be. You would experience a dramatic speed improvement if you (in addition to doing everything within the same transaction) changed to using a prepared statement with parameters (my guess is that you could update a couple of thousand rows per second, though that is of course dependent upon there only being one or a few transactions, not 13000):
> var
> MyID,
> MyBIC: TIB_Column;
> ...
> begin
> with TIB_DSQL.CREATE(Application) do
> try
> IB_Connection:=...
> IB_Transaction:=...
> SQL.Add('update BANKCODES set BIC = :BIC where ID = :ID');
> Prepare;
> MyID:=ParamByName('ID');
> MyBIC:= ParamByName('BIC');
> while not eof(<whatever file has the parameters>) do
> begin
> MyID.AsInteger:=...
> MyBIC.AsString:=...
> Execute;
> end;
> finally
> IB_Transaction.Commit;
> Free;
> end;
> end;

I know about the existence of prepared statements etc., but I never did it exactly this way, so that's an interesting solution - thanks.

Once AutoCommit is false, both solutions, i.e. yours and mine, have about the same execution time. With AutoCommit=true yours takes about as long as mine.

I was / am wondering, why it has worked much better with the first case (older IBO version, older Firebird version), although AutoCommit was true there as well. Theoretically it could be a bug in a newer IBO version or something like that, but it makes sense to have AutoCommit set to false of course and as it works fine that way, I would say the problem is solved.

Thanks for your help!