On Tue, Dec 22, 2015 at 2:17 PM, Caroline Beltran caroline.d.belt...@gmail.com [firebird-support] < firebird-support@yahoogroups.com> wrote:
> > > Since I began using Firebird, I have kept my transactions (type > concurrency) very short and then call COMMIT immediately afterward. This > has worked very well. > > I recently had the need to perform more complex processing and what I did > was to keep everything short and modular. But I am now seeing that my > design in this specific case was flawed. > > I am updating different parts of the same record repeatedly and I believe > that this is causing multiple back versions which causing excessive disk > write I/O and slowing things down terribly: > > a) begin a transaction, update FIELD_1 of MYTABLE, COMMIT, and end > transaction. > Just curious, why do you say both "COMMIT" and "end transaction" - Commit ends the transaction. Good luck, Ann > b) begin a transaction, update FIELD_2 of MYTABLE, COMMIT, and end > transaction. > c) begin a transaction, update FIELD_3 of MYTABLE, COMMIT, and end > transaction. > d) begin a transaction, update FIELD_4 of MYTABLE, COMMIT, and end > transaction. > e) begin a transaction, update FIELD_5 of MYTABLE, COMMIT, and end > transaction. > Note: other tables are inserted and updated during during transactions a-e > but those tables are not presenting any problems. The problem is with > MYTABLE. > > Of course, I normally update all fields in one transaction but in this > particular case, determining the contents of each field is a complex > process that requires manipulation and analysis of the the data provided by > a number of other Firebird SELECT queries to the database. > > I am averaging about 300 transactions per minute during this process that > may last 12 hours and during that time, things get terribly slow. > > So can someone confirm my suspicions, will each of the 5 transactions > above to the same row of data cause 5 new 'back versions'? > > Like I said, I have always kept transactions very short. I am thinking of > something like this instead: > > a) begin a transaction, update FIELD_1 of MYTABLE. > b) update FIELD_2 of MYTABLE. > c) update FIELD_3 of MYTABLE. > d) update FIELD_4 of MYTABLE. > e) update FIELD_5 of MYTABLE, COMMIT, and end transaction. > If something fails anywhere in between, I would ROLLBACK this single > transaction. > > Keeping my transactions shorter and more modular as above is easier from a > development point of view but I have the back version performance issue. > Although the second method means a much longer transaction, I won't have > back versions to deal with. Do you think that this approach would be > better? > > Thank you > > P.S. Sweeping the database does not help with the performance problem, > the only temporary solution to regain performance is to backup using GBAK > and restore. > > > >
