Modern C++

SQLite Performance and Prepared Statements

SQLite is a tremendously useful database for C++ developers. In this follow-up, Kenny Kerr shows various techniques for improving performance even further.

• By Kenny Kerr
• 03/19/2014

More on This Topic:

• Using SQLite with Modern C++
• SQLite Queries and Primary Keys

In my last column, I introduced SQLite, an amazing little database engine written and provided entirely in C source code. I showed you how to begin wrapping it up in modern C++, producing a correct and efficient abstraction that's also much easier to use. In this column, I'm going to show you a few more things you can do to make SQLite enjoyable to use from C++.

I ended my last column with a connection class that sported an open method for creating or opening a database file, as well as an execute method for ad hoc queries. I could then simply write something like this:

connection c;
c.open("C:\\Sample\\Chickens.db");
c.execute("create table Hens ( Id int primary key, Name text not null )");

SQLite is, however, quite handy for creating a temporary database in memory. In such cases you might not want to deal with a physical file on disk. That in itself can add a lot of complexity: First having to find or create a local folder to house the file, then remembering to delete the temporary file when you're done.

SQLite has an option to create an in-memory database that doesn't rely on an on-disk database file. This avoids the complexity I just mentioned, but can also provide dramatically improved performance. Simply use ":memory:" as the filename argument to the sqlite3_open function. I'll just add an alternative to my connection class's open method to wrap this up, as you can see in Listing 1.

struct connection
{
  connection_handle handle;

  auto open(char const * filename) { ... }

  auto create_memory()
  {
    auto local = connection_handle {};

    auto const result = sqlite3_open(":memory:",
                                     local.get_address_of());

    if (SQLITE_OK != result)
    {
      throw sql_exception { result, sqlite3_errmsg(local.get()) };
    }

    handle = move(local);
  }
    
  // ...   
};

Alternatively, the create_memory method might simply call the open method with the ":memory:" argument, or you can simply add a default value for the open method's single parameter, as follows:

auto open(char const * filename = ":memory:")
{
  auto local = connection_handle {};

  auto const result = sqlite3_open(filename,
                                   local.get_address_of());

  if (SQLITE_OK != result)
  {
    throw sql_exception { result, sqlite3_errmsg(local.get()) };
  }

  handle = move(local);
}

Either way, now I can simply create my temporary, in-memory database without a thought to where it will be stored or how it might be cleaned up:

connection c;
c.create_memory(); // or c.open();
c.execute("create table Hens ( Id int primary key, Name text not null )");

However, running ad hoc queries in this way is rather limiting. Inevitably, you'll need to retrieve individual column values, and more than likely you'll want to reuse a prepared statement with different values. What you need is a statement class. I'll start in much the same way as I did for the connection class, by wrapping up the statement handle (from my previous column):

struct statement
{
  statement_handle handle;

  // ...
};

While connections are opened, statements are prepared. SQLite provides the sqlite3_prepare_v2 function to compile a SQL statement and return the resulting statement object. More specifically, the result is a statement handle. This is a bit simpler than creating a connection as, upon failure, no resources are allocated other than perhaps an error message:

auto prepare(connection const & c,
             char const * text)
{
  handle.reset();

  auto const result = sqlite3_prepare_v2(c.handle.get(),
                                         text,
                                         strlen(text),
                                         handle.get_address_of(),
                                         nullptr);

  if (SQLITE_OK != result)
  {
    throw sql_exception { result, sqlite3_errmsg(c.handle.get()) };
  }
}

Notice that this prepare method first resets the handle. This isn't strictly necessary, but it does make it a lot more convenient to reuse a C++ statement object to execute different SQL statements. Also notice the prepare method relies on an open connection, and it's this open connection that provides any error information should things go awry. Now I can compile, but not execute, a SQL statement as follows:

connection c;
c.open();
c.execute("create table Hens ( Id int primary key, Name text not null )");

statement s;
s.prepare(c, "insert into Hens (Id, Name) values (?, ?)");

Notice the SQL insert statement includes the ? parameters or placeholders, instead of the values to insert. This tells the SQLite database engine that values will be bound to this statement prior to execution. This means I can compile a SQL statement once and use it to insert a set of rows, perhaps using a loop, and certainly improve insertion speed. SQLite provides a collection of sqlite3_bind_* functions for binding values to prepared statements. I'll wrap up just two of them for this example. Listing 2 shows one for integer values.

auto bind(int const index, int const value)
{
  ASSERT(handle);

  auto const result = sqlite3_bind_int(handle.get(),
                                       index,
                                       value);

  if (SQLITE_OK != result)
  {
    throw sql_exception
    {
      result,
      sqlite3_errmsg(sqlite3_db_handle(handle.get()))
    };
  }
}

As usual, if something goes wrong I need to get the error message from the connection. Because the bind method belongs to the statement class, I need to get the connection handle via the statement handle. Fortunately, that's easy enough thanks to the sqlite3_db_handle function. It returns the connection for a particular statement. And binding text values isn't much more complicated, as shown in Listing 3.

auto bind(int const index, char const * value)
{
  ASSERT(handle);

  auto const result = sqlite3_bind_text(handle.get(),
                                        index,
                                        value,
                                        strlen(value),
                                        SQLITE_STATIC);

  if (SQLITE_OK != result)
  {
    throw sql_exception
    {
      result,
      sqlite3_errmsg(sqlite3_db_handle(handle.get()))
    };
  }
}

There are two things to keep in mind when calling the sqlite3_bind_text and sqlite3_bind_text16 functions. First, the size of the value is the number of bytes and not the number of characters. Keep this in mind when using the sqlite3_bind_text16 variant. In that case you might want to multiply the string length with sizeof(wchar_t).

Second, you need to tell the bind function how to consider the buffer holding the value. If the value is temporal and not expected to outlive the eventual execution of the statement, SQLite needs to retain a copy of the value. This is indicated with the SQLITE_TRANSIENT constant. In the example in Listing 3 I've used the SQLITE_STATIC constant, indicating the pointer to the string will safely outlive the execution of the statement and SQLite need not make a copy. Needless to say, this approach will improve performance if you can provide this assurance. I can now bind the values as follows:

statement s;
s.prepare(c, "insert into Hens (Id, Name) values (?, ?)");
s.bind(1, 101);
s.bind(2, "Henrietta");

Notice the column indices begin at 1. Of course, the statement still needs to be executed, and that's the job of the sqlite3_step function. This function is used both for evaluating non-queries such as insert statements and for select statements for which it might be called repeatedly. I might disambiguate this with a simple wrapper method that converts this into a Boolean result:

auto step()
{
  ASSERT(handle);

  auto const result = sqlite3_step(handle.get());

  if (result == SQLITE_ROW) return true;
  if (result == SQLITE_DONE) return false;

  throw sql_exception
  {
    result,
    sqlite3_errmsg(sqlite3_db_handle(handle.get()))
  };
}

I can now call step in a loop to read multiple rows from a result set, or simply call it once to execute a SQL statement to perform an insertion. Executing a prepared statement repeatedly does, however, require SQLite's internal virtual machine to be restored to its initial state; that's the job of the sqlite3_reset function:

auto reset()
{
  auto const result = sqlite3_reset(handle.get());

  if (SQLITE_OK != result)
  {
    throw sql_exception
    {
      result,
      sqlite3_errmsg(sqlite3_db_handle(handle.get()))
    };
  }
}

I'm now ready to execute prepared statements quite simply, as shown in Listing 4.

auto main()
{
  try
  {
    connection c;
    c.open();
    c.execute("create table Hens ( Id int primary key, Name text not null )");

    statement s;
    s.prepare(c, "insert into Hens (Id, Name) values (?, ?)");

    s.bind(1, 101);
    s.bind(2, "Henrietta");
    s.step();

    s.reset();

    s.bind(1, 102);
    s.bind(2, "Rowena");
    s.step();
  }
  catch (sql_exception const & e)
  {
    TRACE(L"%d %S\n", e.code, e.message.c_str());
  }
}

Stay Tuned for More..
SQLite offers up a lot of raw power and performance. It's a no-compromise database engine, and takes some careful consideration to make the most of its capabilities. I've shown a number of techniques in this month's column, from using memory-only database to reusing prepared statements, that are surprisingly simple with a little help from C++. Next month, I'll wrap up this series with a discussion of SQLite engine internals.