Hello Joe, Hannes, first of all, what Hannes says is correct, see the SQLite web site: if there is no explicit "BEGIN transaction" - "COMMIT" in the application, each statement that updates the SQLite database is automatically wrapped in this. I didn't want to say anything different in my results. So the test without an explicit transaction effectively has as many transactions as there are update statements. Basically, this means that each update statement opens the database (=file!), is immediately written to the database and the database file is closed afterwards. if you do a transaction (BEGIN - update statements - COMMIT), SQLite "knows" that the updates should not be written to the database immediately, but only when the COMMIT statement is executed, so the update info is kept in memory until the COMMIT is executed, and then the database file is opened, all changes are written to the database, and the file is closed. I'm pretty sure the number of open/close actions is the reason for the time differences, try opening/closing a text file in NSBasic and you'll see that this is a fairly slow operation, compared with writing something to a file.
Joe, the transaction mechanism in systems like Oracle follows the same logic, but the behaviour and performance penalty may be different, because in Oracle and other databases there's a separate server process that handles the database file(s), so the application just "talks" to the server process and never writes to the database files directly. A "COMMIT" statement may not take very long for the application program, even if the server process may have to work hard to execute it. Actually, in my experience the hard bit is a ROLLBACK, not a COMMIT, it seems to me that at least in Oracle, the server assumes that COMMIT is the more likely end of an update process so it writes changes to the database (sort of), and a COMMIT just makes this active, whereas a ROLLBACK means that all recent updates since the last COMMIT have to be undone from the rollback segment. Kind regards Thomas Am 27.02.2010 um 22:10PM schrieb joespan123: > > > Hi, > > I do not think that is quite correct. > > The purpose of a Transaction is to maintain database integrity if you are > performing multiple operations like insert and delete. If at anytime you get > an error along the way you would issue a rollback which will undo anything > you have done after you start the transaction. > > The other advantage is that when you close a transaction, the database will > commit the changes to physical file and not retain the changes you have made > in memory. > > If you do not use the transaction mechanism, what the database is doing is > performing a transaction behind the scene from the time you open the database > and then close the database. > > So in the case when this was not done, the time to process 30,000 records > took a lot of time, I hazard a guess the database was maintaining transaction > overhead for the whole 30,000 records. > > When a transaction was performed say for every 100 records, the database > committed the changes and the overhead of maintianing transaction is minimal. > > You would get a balance of maximum performance by choosing how many records > you insert during the time you start and close a transaction, this depends on > the device and speed of processing etc etc. > > My experience is with Oracle database and not with SQLLite. I assume the > mechanism of transaction is similar. > > Cheers > Joe > > --- In [email protected], Hannes Streicher <hstreic...@...> wrote: > > > > i like to comment on this one, > > my guess for the speed difference is: > > the difference is not that you are using Transactions or not > > but that without using explicit transactions every single query is > > wrapped into a transaction causing additional overhead > > > > > > -- > > Mit freundlichen Grüssen / with best regards > > Hannes Streicher mailto:hstreic...@... > > > > -- You received this message because you are subscribed to the Google Groups "nsb-ce" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/nsb-ce?hl=en.
