12 Ways To Optimize SQLite Databases – Try Them Now!

SQLite is an SQL-based relational database management system (RDBMS) implemented as an embeddable library. It stores databases as discrete files, rather than relying on the client-server model.

SQLite is commonly used in three ways:

• Its ease of use is ideal for testing and prototyping database-backed applications.
• Because everything is stored locally, and the library itself can be embedded in an application, SQLite is often also used as the primary data store for small applications run locally by a single user. This includes applications like address books, to-do lists, or even email readers.
• Finally, SQLite databases are often used as an application-specific file format. This is especially useful in applications where a saved file is a complex project, rather than a relatively simple document. In this case, each file created by the application is actually an entire SQLite database.

The Need for Optimization

Most of the time, when it is being used for testing and prototyping, optimizing it for speed is not terribly important. In these cases, it also isn’t always possible to do so, since you might plan to run your application with a different database in production. SQLite here is simply being used as a stand-in for something else like PostgreSQL or MySQL.

But when SQLite is being used “in production” as in the second two cases, performance matters. Adopting a few simple techniques can really affect the speed of database updates and queries.

Here are some practical tips for improving SQLite performance in your applications. Some of them are SQL query optimization techniques that would help speed up any SQL database system. Others are particular to optimizing SQLite.

Since SQLite is a very popular datastore in Android apps, we’ve also included some specific tips for optimizing SQLite performance on Android.

Use a Transaction

The very first piece of advice that everyone gives for speeding up SQLite is “use a transaction.”

Everyone says this because it’s a really good idea. But you might wonder how to use a transaction in SQL.

Let’s say you have collected a bunch of data in some iterable structure like a list or array. You might be tempted to loop through your data and insert it into your SQLite database on each iteration of the loop.

/****************************************************
Get first and last names from a tab-delimited file.
Then insert them into SQLlite database.
****************************************************/
/* be sure to define these in real life...
#define DATABASE = // name of database //
#define FILE_OF_NAMES = // path to file //
#define CREATE_TABLE = // SQL statement to create Names table //
*/
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, CREATE_TABLE, NULL, NULL, &sErrMsg);
pFile = fopen (FILE_OF_NAMES,"r");
while (!feof(pFile)) {
 fgets (sInputBuf, BUFFER_SIZE, pFile);
 sFirstName = strtok (sInputBuf, "t");
 sLastName = strtok (NULL, "t"); 
 sprintf(sSQL, "INSERT INTO Names VALUES (NULL, '%s', '%s', )", sFirstName, sLastName, s);
 sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);
 n++;
}
fclose (pFile);
sqlite3_close(db);

This is a bad idea, as it atomizes each insert into a single transaction — each one with its own overhead. It’s not a big deal if you only have a couple inserts, but even in fast-running C code, this can slow you down to under 100 inserts a second. If you’re using SQLite as an application file format, this could potentially mean users experience several seconds of lag each time they save a complex document or project.

Instead of inserting your data set individually, wrap up all your inserts into a single transaction. This will significantly speed up your inserts. And it’s an extremely easy change to make.

/* before the loop - begin the transaction*/
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (FILE_OF_NAMES,"r");
while (!feof(pFile)) {
 .
 .
 .
}
fclose (pFile);
/* after the loop - end the transaction */
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

You are still executing the INSERT statement inside the loop, but they aren’t updating the database on each iteration. Instead, SQLite saves up all of your statements in a cache, and then runs them all at once as a single operation when you END TRANSACTION.

Since the inserts are all stored in the cache, you may need to raise your cache size to get the speed advantage of using transactions this way.

/* after opening db connection,
before starting transaction*/
sqlite3_exec(db, "PRAGMA cache_size=10000", NULL, NULL, &sErrMsg);

Transactions in Android

Android’s built-in SQLite API makes using transactions even easier.

// to start a transaction
db.beginTransaction();
// to end a transaction
db.endTransaction();

You may also want to check to see if there were exceptions before committing the transaction, and then log the error if there was a problem. That is also easy in Android.

try {
 db.beginTransaction();
 /* Do stuff in a loop. */
 db.setTransactionSuccessful(); // This commits the transaction if there were no exceptions
} catch (Exception e) {
 Log.w("Exception:", e);
} finally {
 db.endTransaction();
}

Prepare and Bind

In the last example, the SQL statement was re-created during the execution of each loop. This means that it was also parsed by SQLite every time. That parsing has some computational overhead, slowing things down with each iteration.

You can speed things up by preparing your SQL statement outside the loop, and then binding your data to it each time you use it.

/* before starting the transaction */
sprintf(sSQL, "INSERT INTO Names VALUES (NULL, @FirstName, @LastName)");
sqlite3_prepare_v2(db, sSQL, BUFFER_SIZE, &stmt, &tail);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
pFile = fopen (FILE_OF_NAMES,"r");
while (!feof(pFile)) {
 fgets (sInputBuf, BUFFER_SIZE, pFile);
 sFirstName = strtok (sInputBuf, "t");
 sLastName = strtok (NULL, "t"); 
 sqlite3_bind_text(stmt, 1, sFirstName, -1, SQLITE_STATIC);
 sqlite3_bind_text(stmt, 2, sLastName, -1, SQLITE_STATIC);
 sqlite3_step(stmt);
 sqlite3_clear_bindings(stmt);
 sqlite3_reset(stmt);
 n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_close(db);

This strategy can be used outside of loops, too. If you have a query inside a function, you can prepare it once and then bind it each time it is used.

Prepared Statements in Android

The Android SQLite API provides the SQLiteStatement class for doing this easily.

//write the query, with ? for values to insert
String sql = "INSERT INTO Names VALUES (?, ?)";
// compile the statement
SQLiteStatement statement = db.compileStatement(sql);
/** loop through records **/
 /** get the names from the file and assign to firstName and lastName **/
 // bind
 statement.bindString(1, firstName);
 statement.bindString(2, lastName); 
 // exec
 Long row_id = statement.executeInsert();

Don’t Sync to Disk After Every Insert

SQLite, by default, waits for the OS to write to disk after issuing each of these inserts. You can turn this pause off with a simple command.

sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

Place this after opening the connection to the database, but before starting the transaction. You should also know that this could cause a database corruption in the event of a crash or power outage. So, you’ll want to weigh the increased speed here against any potential risks.

Store Rollback Journal In Memory

If you’re already living dangerously with PRAGMA synchronous = OFF, and you’re trying to squeeze out all the extra milliseconds, you can also store the rollback journal in memory instead of saving it to disk. Combined with the previous optimization, this is a bit risky.

sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

You can also set journal_mode to OFF if you’re trying to win a speed competition or something (this is not recommended for real-life use).

Journal Mode Warning for Android

SQLite’s Journal Mode is managed by Android’s enableWriteAheadLogging() method. So, as the documentation for executing raw SQL commands says:

Do not set journal_mode using "PRAGMA journal_mode" statement if your app is using enableWriteAheadLogging().

Only Index When You Really Need It

Naive database developers like to create a lot of indexes “to speed things up”. But doing so haphazardly, or indexing literally everything can be counterproductive. Indexing the contents of a table by a particular row makes reads faster and writes slower. And it only makes reads faster for queries that are searching based on that column.

So, if users are never going to search the contents of a table based on a particular column, don’t index it. Further, if users are only going to search by a particular column infrequently, don’t index it. Even if they are likely to search frequently, you still need to think about whether the table will be written to or searched from more often. If it will be written more often than searched, or if write speeds are especially critical, then don’t index it.

Often, the type of application will dictate these needs. SQLite is not commonly used for large data stores that need to support a wide variety of operations. If it is being used as an application file type, for example, the ability for a user to quickly save a project while working is probably more important than being able to search the contents of a working document as fast as possible. On the other hand, a data storage app that usually has manual, single-entry updates (like a contacts directory or to-do list) can probably stand to have slightly slower writes, but should support very fast searching.

Index After Bulk Insert

Once you create an index on a table, each insert thereafter will take the time to index the new content. If your table will be initialized with a large insert of bulk data (perhaps the first time a new project or document is saved, or when importing data for a new user), you can speed up that first large insert by waiting to create the index until after the insert.

Other PRAGMA settings

There are a number of PRAGMA settings that can help improve your SQLite performance.

Cache Size

As briefly mentioned above, you might need to increase your cache_size. Large transactions will only be sped up if the entire transaction can be stored in the cache.

Memory used for the cache is allocated when it is needed, so there’s no overhead to setting it too high. You can also dynamically adjust — raising it to optimize for certain queries and then lowering it when not needed.

sqlite3_exec(db, "PRAGMA cache_size = 100000", NULL, NULL, &sErrMsg);

Temporary Table Storage

You can tell SQLite to store temporary tables in memory. This will speed up many read operations that rely on temporary tables, indices, and views.

sqlite3_exec(db, "PRAGMA temp_store = MEMORY", NULL, NULL, &sErrMsg);

Android and Pragma Settings

You can use the execSQL() method to execute raw SQL against your SQL database. That’s the most direct way of changing any of the PRAGMA settings. However, some of them (like journal_mode mentioned above) are managed by other classes or helpers in the API.

Faster Queries — Filter More Sooner

If you are doing a query based on multiple criteria, you can often speed it up by rearranging how your criteria are ordered. If the first WHERE clause returns the fewest number of results, each subsequent one will have fewer items to deal with.

In a query that has a large number of parameters, try experimenting with several different permutations of the order to see which one has the best average speed.

Case Insensitive Indexes for LIKE

The LIKE clause for comparing text is case-insensitive. Indexes are case-sensitive by default. If the only queries your indexes are optimizing for are LIKE queries, you can save time on inserts and on queries by making your index case-insensitive.

CREATE INDEX sLastName ON NAMES (KEY COLLATE NOCASE);

Use the Latest Version if Possible

Each major version release of SQLite includes performance enhancements. Some releases have dramatically increased speed. So, if you are using a minor version that is a few years old (or worse, still using v2), the easiest route to faster execution is simply upgrading.

Don’t Create a New Database

This is a big change in thinking for people coming from other RDBMS systems.

Consider the case of using SQLite as an application file format. Every time you save a new project (file) for the first time in the app, a new database instance is needed.

You could create a new database and execute a series of SQL statements to add the appropriate tables and indexes. This is what you would want to do if you were building a deployable application with (for example) PostgreSQL — you’d write the code to set up the database and have it run at install.

But there’s a faster way.

Since a SQLite database is a discrete file, it is relatively trivial to clone a database — it is simply duplicating a file. This means that it is usually not necessary to create a new database and then execute all of the SQL statements required. Usually, you can just make a copy.

On Android, you may want to use the SQLite Asset Helper to manage databases as assets when doing this.

Consider Denormalizing

If you have experience with relational database systems, you might care a lot about normalizing your data. There’s a lot more to it than this, but the essence of data normalization is single source of truth.

In a normalized relational database, any piece of data, no matter how trivial, is represented exactly one time. So, for example, a record representing a book might reference the record representing the author — but it certainly wouldn’t spell out the author’s name directly.

This saves space and is more elegant. But it also makes reading from the database more time consuming. If you want to find all the books by an author, you have to search the author table to get the id and then search the book table to assemble the records.

You can speed up this kind of read by duplicating the author’s name on all the book records. This improves performance, but sacrifices normalization. This has two possible downsides, besides inelegance:

• Application logic become responsible for maintaining data integrity (that is, truthfulness or accuracy).
• The database will need to be larger to store the same amount of information.

Denormalization in SQLite Is Particularly Good

All of these concerns and tradeoffs are present when working with any RDBMS system, not just SQLite. However, in SQLite, there are some potentially mitigating factors that make data denormalization less problematic and more useful.

• Typically, a SQLite application is going to have a less complicated data model (schema) than a very large application that needs a database server. This makes the application complexity needed to support denormalized data less onerous.
• Data sets supported by SQLite databases are typically smaller than those stored in other database systems. This means that the increase in size from duplicated data is less problematic. Additionally, at the scale of most SQLite applications (local file storage), the cost of additional size is negligible.
• Unlike large database servers (especially those maintained by organizations), it is unlikely that a second application will attempt to connect to the SQLite database files created by your application. Therefore, you don’t have to guard against accidental data corruption and problematic team dynamics.
• Similarly, because SQLite is typically used as an embedded database, there is often a tight coupling between the application and the database structure. This means that the downsides of handling data integrity inside the application are generally lower than they would be when a database and application are loosely coupled physically, but highly interdependent in reality.
• Finally, some of the normalization-maintaining performance enhancements available in other database systems — such as materialized views — are not available in SQLite.

For these reasons, denormalizing for performance is a much more common practice with SQLite than with other relational databases.

Summary

These tips for optimizing SQLite performance are just that — tips. This is not an exact plan to follow, and you won’t speed up your SQLite application by simply adding each item from this list into your code. They are ideas that might help if used appropriately. So, think about your application and see if any of them might help. And test. You need to find out what is making your application slow before you can make it fast.

Further Reading and Resources

We have more guides, tutorials, and infographics related to coding and development:

• SQL Resources: Our general SQL resource that is critical for all relational database developers.
• MySQL Introduction and Resources: Another very popular database system.
• PostgreSQL Introduction and Resources: A popular database system on its own, SQLite is partly based on it.

Ultimate Guide to Web Hosting

Check out our Ultimate Guide to Web Hosting. It will explain everything you need to know in order to pick the best host for your situation.

Ultimate Guide to Web Hosting