#loading

Improving SQLite insert performance

Improving SQLite insert performance

Sep 18, 2019

In mobile application development, having a local database inside the application is a regular thing. There is a lot of ORM and database integration is possible in a matter of a few minutes. Once it is set up, developers can easily perform database operations using regular entity classes and managers. They don’t even have to know what’s going on under the hood of ORM. 

In most cases mobile databases won’t store millions of rows. That is usually used for caching backend responses. So, developers usually don’t care about the database performance as long as it works really fast with a limited amount of data.

However, there still are specific cases when you have to keep relatively a large database locally on the device. There are several rules and recommendations that will help you to improve the database performance. In this article, we will try to improve SQLite inserts performance.

For test purposes, I created a simple Android project with a single Instrumented test that measures SQL insert time for 10 000 records. The basic version of this test uses GreenDao ORM v3. Please visit http://greenrobot.org/greendao/documentation/ to review the documentation.

It’s worth noting that we are also going to use database encryption. Thus, all measurements will be done based on the encrypted database on a Samsung Galaxy J7 device

Let’s assume we are going to have a huge local database of the world’s cinematography. We use only one basic entity for this test. Let’s create GreenDao v3 annotated entity called Movie.

@Entity(active = true)
public class Movie {
   @Id(autoincrement = true)
   private Long id;
   private String title;
   private String genre;
   private Long publicationDate;
   private String director;
   private String screenWriter;
   private String producer;
   private String editor;
   private Double filmingLocationLat;
   private Double filmingLocationLon;
   private String productionCompany;
   private Long cost;
   private Boolean awardReceived;
   private Double rating;
}

I’m not including getters/setters, constructors, etc., GreenDao generates for you. As I have said, we are using an encrypted database. Here’s how we initialize it

DaoMaster.DevOpenHelper helper = new DaoMaster.DevOpenHelper(InstrumentationRegistry.getTargetContext(), "movies-db");

Database db = helper.getEncryptedWritableDb("very_secure_password");

daoSession = new DaoMaster(db).newSession();

There is also a helper method to prepare a testing dataset. We are just generating random values for entity fields

List<Movie> movies = new ArrayList<>();
for (int i = 0; i < 10000; i++) {
   movies.add(new Movie(getRandomString(10, false),
           getRandomString(10, false),
           getRandomLong(),
           getRandomString(10, true),
           getRandomString(10, true),
           getRandomString(10, true),
           getRandomString(10, true),
           getRandomDouble(),
           getRandomDouble(),
           getRandomString(10, true),
           getRandomLong(),
           getRandomBoolean(),
           getRandomDouble()));
}

Parameters for method getRandomString(10, false) mean that there should be 10 symbols and it may return null based on a randomizer.

Now that we have initialized the database and dataset, let’s insert 10 000 entities in the most simple way

final MovieDao movieDao = daoSession.getMovieDao();
for (Movie movie : movies) {
   movieDao.insert(movie);
}

The result is awful. It’s taken about  3 minutes to insert 10 000 records. 

The reason it happens is that Android SQLite framework wraps every insert statement into a separate transaction and it has a huge impact on the insert time. Don’t use this method. Never.

In real life what you should do is to use bulk insert through a single transaction as follows:

final MovieDao movieDao = daoSession.getMovieDao();

movieDao.insertInTx(movies);

This should improve the insert speed a lot and it really does. Bulk insert of 10k records takes about 16sec avg.

That is a huge improvement and there are not many cases when you would want to insert 10000 records at a time. But, let’s try to check if it’s possible to alternatively decrease the insertion time.

For the next test, let’s check SQLite supported PRAGMA settings. https://www.sqlite.org/pragma.html  There are several of them that may have impact on the insert speed.

synchronous (https://www.sqlite.org/pragma.html#pragma_synchronous) in a nutshell it’s a flag that indicates the database synchronization strategy with the disk.

FULL is a default value for synchronous PRAGMA setting which means that the database waits for the data to be synchronized on the disk. It’s possible to switch the synchronization off, but there is a risk that the database will become corrupted in case the application crashes while there is a proper operation to the database.

Another option that can be used to decrease the insertion time is Write-Ahead Logging  (https://www.sqlite.org/wal.html. This is a strategy which is responsible for the way the transaction is applied to the database.

Let’s add the following configuration and see what happens


helper.setWriteAheadLoggingEnabled(true);
db.execSQL("PRAGMA synchronous = NORMAL");
db.execSQL("PRAGMA cache_size = 100000");

10 0000 entities are inserted using GreenDao with updated db config in 13.2 sec avg. We have removed the statistics bar with the stand alone inserts to visualize the difference.

Currently, we are using ORM and there is a chance that ORM is not completely optimized for our particular entity. I mean usually ORM has some generic logic with general statements that meet all requirements for the application -- database interaction. However, sometimes to perform an operation that requires a few lines on SQL ORM might add additional inner processes like serialization/deserialization etc. Let’s try to omit ORM during the insertion and prepare SQL statements ourselves.

Prepare the insert template:

String insertStatementPattern = "INSERT OR REPLACE INTO " + MovieDao.TABLENAME + "" + "(" + MovieDao.Properties.Title.columnName + "," 
+MovieDao.Properties.Genre.columnName + "," +MovieDao.Properties.PublicationDate.columnName + "," +MovieDao.Properties.Director.columnName + "," 
+MovieDao.Properties.ScreenWriter.columnName + "," +MovieDao.Properties.Producer.columnName +"," +MovieDao.Properties.Editor.columnName +"," +MovieDao.Properties.FilmingLocationLat.columnName +"," +MovieDao.Properties.FilmingLocationLon.columnName + "," +MovieDao.Properties.ProductionCompany.columnName +"," +MovieDao.Properties.Cost.columnName +"," +MovieDao.Properties.AwardReceived.columnName + "," +MovieDao.Properties.Rating.columnName 
+") values(?,?,?,?,?,?,?,?,?,?,?,?,?)";

It’s a bad practice to compose the string like this since each time you make a “+” operation, a new string is allocated and so on. It’s just to simplify the readability.

With this template let’s bind all values of the entity to the SQLStatement. 

Notice: when you try to bind a null value to the statement it will throw an exception so we will set default values instead of them

db.beginTransaction();

DatabaseStatement insertStmt = db.compileStatement(insertStatementPattern);
for (Movie movie : movies) {

   insertStmt.bindString(1, movie.getTitle());
   insertStmt.bindString(2, movie.getGenre());
   insertStmt.bindLong(3, movie.getPublicationDate());
   insertStmt.bindString(4, movie.getDirector() == null ? "" : movie.getDirector());
   insertStmt.bindString(5, movie.getScreenWriter() == null ? "" : movie.getScreenWriter());
   insertStmt.bindString(6, movie.getProducer() == null ? "" : movie.getProducer());
   insertStmt.bindString(7, movie.getEditor() == null ? "" : movie.getEditor());
   insertStmt.bindDouble(8, movie.getFilmingLocationLat());
   insertStmt.bindDouble(9, movie.getFilmingLocationLon());
   insertStmt.bindString(10, movie.getProductionCompany() == null ? "" : movie.getProductionCompany());
   insertStmt.bindLong(11, movie.getCost());
   insertStmt.bindLong(12, movie.getAwardReceived() ? 1 : 0);
   insertStmt.bindDouble(13, movie.getRating());

   insertStmt.executeInsert();
   insertStmt.clearBindings();
}
insertStmt.close();

db.setTransactionSuccessful();
db.endTransaction();


We could squeeze it a little more up to 11.2 sec avg. In the previous test we’ve noticed that the values can be null. Why do we have to store default values for nullable fields? We can leave values unbinded and in this case we will have a smaller amount of data to insert, which will enable us to decrease the insertion time a little more.

db.beginTransaction();

DatabaseStatement insertStmt = db.compileStatement(insertStatementPattern);
for (Movie movie : movies) {

   insertStmt.bindString(1, movie.getTitle());
   insertStmt.bindString(2, movie.getGenre());
   insertStmt.bindLong(3, movie.getPublicationDate());
   if (movie.getDirector() != null) {
       insertStmt.bindString(4, movie.getDirector());
   }
   if (movie.getScreenWriter() != null) {
       insertStmt.bindString(5, movie.getScreenWriter());
   }
   if (movie.getProducer() != null) {
       insertStmt.bindString(6, movie.getProducer());
   }
   if (movie.getEditor() != null) {
       insertStmt.bindString(7, movie.getEditor());
   }
   insertStmt.bindDouble(8, movie.getFilmingLocationLat());
   insertStmt.bindDouble(9, movie.getFilmingLocationLon());
   if (movie.getProductionCompany() != null) {
       insertStmt.bindString(10, movie.getProductionCompany());
   }
   insertStmt.bindLong(11, movie.getCost());
   insertStmt.bindLong(12, movie.getAwardReceived() ? 1 : 0);
   insertStmt.bindDouble(13, movie.getRating());

   insertStmt.executeInsert();
   insertStmt.clearBindings();
}
insertStmt.close();

db.setTransactionSuccessful();
db.endTransaction();

It’s a huge difference comparing to the initial bulk insert using GreenDao, right? Of course, it’s not very secure to switch off the db synchronization. However, even if we omit this option, we still decrease the insertion time a lot.

Have you ever wondered how fast the unencrypted database is? What will be the insertion time for 10k records if you use a database without encryption? I’ve made a test with all the options above enabled with the only one difference: I’ve removed database encryption. Here is the result:

Conclusion


The first and the most important advice is do not use the DB encryption unless your data is sensitive and you want to protect it. If you still want to use the DB encryption, you should keep in mind that the database becomes (in our case) about 7 times slower.

In most cases you don’t need high database performance since you rarely have a big database in the mobile application. But when the performance question arises, you can experiment with the options above.

BE THE FIRST WHO GETS UPDATES
Using Corsac Blog and website you agree to our Privacy Policy and Terms and Conditions.