How to speed up insert in SQLite

Good day. In my first Android application, I immediately faced the need to work with a database. I needed to provide my users with a starting data set (about 5000-6000 records), with which they could work out of the box. It was decided to attach a text file with data in the form of JSON to the application, at the first start, parse them and put them in the database. You can read more about how to do this incorrectly and how to enjoy the performance gain after refactoring later.

I don’t want to tell a long story about what thorns a person who wrote his first Java application had to go through, and even immediately under Android. In my opinion, it’s better not to bore you with stories, but to get down to business immediately. After all, it was for the answer that you looked here?

So that was. Naively, DBHelper was implemented via singleton, and the database object was stored in the same way:
publicclassDBHelperextendsSQLiteOpenHelper{
...
privatestatic DBHelper instance;
privatestatic SQLiteDatabase db;
	publicstatic DBHelper getInstance(){
		if (instance == null)
			instance = new DBHelper(Pleazzme.getAppContext());
		return instance;
	}
	publicstatic SQLiteDatabase getDB(){
		if (db == null)
			db = getInstance().getWritableDatabase();
		return db;
	}
...
}

In a separate AsyncTask, a parser was launched, which in a loop gave me an object with the save () method:
...
	publicvoidsave(){
		ContentValues values = new ContentValues();
		values.put(id, Id);
		values.put(name, Name);
		values.put(categoriesIds, App.gson.toJson(CategoriesIds));
		values.put(datecreated, DateCreated.getTime());
		Document.save();
		values.put(document_id, Document.getId());
		values.put(hasbarcode, hasBarcode);
		values.put(headofficeaddress, HeadOfficeAddress);
		values.put(phonenumbers, PhoneNumbers);
		values.put(website, WebSite);
		values.put(popularity, Popularity);
		values.put(keywords, Keywords);
		DBHelper.getDB().insertWithOnConflict(table, null, values, SQLiteDatabase.CONFLICT_REPLACE);
	}
...


In this situation, I got the object saved in the database for 40-50ms. As a result, all the preservation poured out to me at least 4 minutes. Naturally, this did not suit users. And I started smoking search engines and the Internet for a solution to speed up my insertions. The answer was found here and in the documentation .

To speed up the process, the following steps were taken:
  • use InsertHelper instead of ContentValues
  • Before starting a large insert, turn off synchronization in the database, lock and put everything in one transaction
  • for the correct operation of all of the above, it is necessary to transfer the database object pulled out in async to the save () method of each object, otherwise we will get an exception


Given that the code is much more eloquent, in the final the design looks like this:
publicclassDBHelperextendsSQLiteOpenHelper{
...
privatestatic DBHelper instance;
privatestatic SQLiteDatabase db;
	publicstatic DBHelper getInstance(){
		if (instance == null)
			instance = new DBHelper(Pleazzme.getAppContext());
		return instance;
	}
	publicstatic SQLiteDatabase getDB(){
		if (db == null)
			db = getInstance().getWritableDatabase();
		return db;
	}
...
/*new code*/publicstaticvoidbigDataBegin(SQLiteDatabase _db){
		//_db.setLockingEnabled(false);//_db.execSQL("PRAGMA synchronous=0");
		_db.beginTransaction();
	}
	publicstaticvoidbigDataEnd(SQLiteDatabase _db){
		//_db.setLockingEnabled(true);//_db.execSQL("PRAGMA synchronous=1");
		_db.setTransactionSuccessful();
		_db.endTransaction();
	}
}


Saved Object Class:
...
privatestatic InsertHelper ih;
publicvoidsave(final SQLiteDatabase db){
		if(ih == null)
			ih = new InsertHelper(db, table);
		ih.prepareForInsert();
		try {
			ih.bind(ih.getColumnIndex(key), value);
		} catch (NullPointerException e) {
		}
        .....
		ih.execute();
		ih.close();
}
...


And so that all this stuff “takes off” the code for saving objects:
SQLiteDatabase database = DBHelper.getDB();
DBHelper.bigDataBegin(database);
for (int i = 0; i < currSize; i++) {
        gson.fromJson(o, Data.class).save(database);
	}
DBHelper.bigDataEnd(database);


After all the manipulations and speed measurements, the result was as follows:
  • up to - 40ms per object
  • after - 4ms per object


I hope the information will be useful to someone. I must say right away, first of all, it is designed for beginner programmers. If someone can offer more effective options, I will only be glad.

UPD.
In an unconscious outburst of joy, I shared my find, as it turned out too early. After the tests, it turned out that in case of trying to add some data with another parallel thread, we get ayah, in addition, some devices swear at trying to use the pragma directly.

Thus, only the use of one transaction remains (I commented out the error code). It is in this form that it remains as safe as possible. True, at the same time, it loses all claims to something that carries more information than documentation.

Another interesting fact for me was that a noticeable performance increase was observed on powerful devices (SGS2, for example, with 4.0.4 on board), while the younger brothers (HTC Wildfire) did not show a noticeable increase ...

Also popular now: