Database Transactions in Xojo

Every now and then someone on the Xojo forum wonders why inserting data into an SQLite database is so slow.  Databases are designed to hold billions of records and do it fast, right?  So why is their application so slow?  The answer is they are relying upon the built-in transaction in Xojo.

By default SQLite databases do an automatic transaction for you.  This means that as soon as you attempt to insert, update, or delete data the work to write that change to disk happens as soon as possible.

For example, let’s take the following bit of code to insert data into a table:

For i As Integer = 1 To kMax
   Dim dbr As New DatabaseRecord
   dbr.Column("Name") = GetName
   dbr.IntegerColumn("Age") = GetAge
   dbr.DateColumn("TheDate") = New date
   db.InsertRecord "t_table", dbr
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage

GetName gets a random name from an array that has 26 names in it and adds a random integer between 1000 and 10000.  GetAge returns a random integer between 1 and 74. 

This is pretty simple insert and if you run this 10,000 times it takes roughly 49 seconds saving to a desktop SQLite file on my 5k iMac.  And in so doing the application is locked up for that entire time because I’m in the tight loop.  This is simply unacceptable.

I’m sure someone is screaming why are you using the DatabaseRecord!  It’s slow!  It’s inefficient!  You should be using PreparedStatement’s!  Okay, so using pretty much the same logic:

Dim sql As String = "Insert into t_table (Name, Age, TheDate) Values(?, ?, ?);"
Dim ps As SQLitePreparedStatement = db.Prepare(sql)
ps.BindType(0, SQLitePreparedStatement.SQLITE_TEXT)
ps.BindType(1, SQLitePreparedStatement.SQLITE_INTEGER)
ps.BindType(2, SQLitePreparedStatement.SQLITE_TEXT)
For i As Integer = 1 To kMax
   ps.Bind(0, GetName)
   ps.bind(1, GetAge)
   ps.Bind(2, New Date)
   If db.Error Then
      MsgBox "DB Error: " + db.ErrorMessage

This takes about 48 seconds.  No big time savings there.  Obviously that’s not the improvement we need.

What we need to do is put these inserts in a database transaction using the following bit of code before the loop starts:

If db.Error Then
   MsgBox "DB Error: " + db.ErrorMessage

And then at the end use:


Using the DatabaseRecord method takes a whopping 1 second with 10,000 records.  Using the prepared statement is so fast that my measured elapsed time in seconds is effectively 0.  

If I up the number of records inserted to 1,000,000 I get an interesting result.  The DatabaseRecord method takes 27 seconds where the PreparedStatement method takes 47 seconds.  And if I declare the PreparedStatement inside the loop it now takes 55 seconds.

What have we learned in this blog post?  First, using a database transaction is considerably faster than using the default transaction behavior.  Second, using DatabaseRecord is pretty fast and depending upon the number of record inserted it might be considerably faster.  Honestly, I didn’t expect this.  

In the next blog post I’ll look at Record updates and what are the best methods.

5 thoughts on “Database Transactions in Xojo

  1. With transactions there’s definitely a sweet spot in terms of how large the data set should be. As you know the right answer for a single transaction is “everything that has to be treated as one logical unit of work”.
    When there is a lot of data to be loaded like your example I’ve suggested more than once to use a temporary table and a transaction size of a few thousand rows.
    And then the final insert into the permanent table in a single transaction using “insert into … select …” or some similar variation.
    This keeps the entire bulk load pretty fast by limiting the size of a single transaction and keeps the final insert into the permanent table as a single transaction.

    • Good idea. I hadn’t thought of that.

      Any idea on why with the larger insert set the PreparedStatement way was so much longer than the simple DatabaseRecord way?

  2. Database Management in Xojo is only thing that is restraining me to dive in Xojo

    I am an VBA/Access developper but Have a Mac at home I was hopping a tool for my personnal use :I am running two caritative and for one of them would need a secretariat software for my partner that also have mac Xojo is able to create for both plateform. and you have to write EVERYTHING your self even the binding it is so slow for me and so afar from the pledge “Rapid app development” about database management even with sqlite

    • FWIW, I’ve only found binding to be decent in Access. When we tried to do binding with VB6 for a big accounting application the binding caused more issues than it helped. After a few months of fighting the events (especially the ones we had no control over) we ripped it all out and did everything manually – just like we still do in Xojo.

      “Rapid App Development” means different things to different people. From what you say, you want a cross-platform MS Access. Try FileMaker. But I hear what you’re saying. Xojo doesn’t make database applications very RAD.

      Our utility ARGen might be something to look at. It creates ActiveRecord classes and builds basic UI (desktop or web) and does some basic ActiveRecord/UI connections. It is not a perfect solution but we use it in all our DB applications as it’s very OO friendly. But it’s not binding in the sense that you know from Access.

Comments are closed.