How to speed up insert in sqlite

Good day. In my first Android application, I immediately encountered the need to work with the database. I needed to provide my users with a start-up data set (about 5,000-6,000 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, when it was first started, parse it and put it in the database. How to do it wrong and how to rejoice in the performance gain after refactoring can be read further.

I don’t want to tell a long story about the thorns that a person writing his first Java application had to go through, and also immediately under Android. In my opinion, it is better not to bore you with stories, but go straight to the point. After all, exactly for the answer you looked here?

So what was. DBHelper is implemented naiveton naively, and the database object was also stored in the same way:

public class DBHelper extends SQLiteOpenHelper { ... private static DBHelper instance; private static SQLiteDatabase db; public static DBHelper getInstance() { if (instance == null) instance = new DBHelper(Pleazzme.getAppContext()); return instance; } public static SQLiteDatabase getDB() { if (db == null) db = getInstance().getWritableDatabase(); return db; } ... } 

In a separate AsyncTask, the parser was started, which in a cycle gave me an object with the save () method:

 ... public void save() { 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 scenario, I received the save object in the database for 40-50ms. As a result, all the preservation poured me into 4 minutes minimum. Naturally, this did not suit the users. And I started smoking search engines and the Internet for a solution to speed up my inserts. The answer was found here and in the documentation .

To speed up the process, the following steps were taken:

• instead of using ContentValues ​​use InsertHelper
• Before launching 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 a database object pulled out in the async-type to the save () method of each object, otherwise we will get an action

Given that the code is much more eloquent, in the final design looks like this:

 public class DBHelper extends SQLiteOpenHelper { ... private static DBHelper instance; private static SQLiteDatabase db; public static DBHelper getInstance() { if (instance == null) instance = new DBHelper(Pleazzme.getAppContext()); return instance; } public static SQLiteDatabase getDB() { if (db == null) db = getInstance().getWritableDatabase(); return db; } ... /*new code*/ public static void bigDataBegin(SQLiteDatabase _db){ //_db.setLockingEnabled(false); //_db.execSQL("PRAGMA synchronous=0"); _db.beginTransaction(); } public static void bigDataEnd(SQLiteDatabase _db){ //_db.setLockingEnabled(true); //_db.execSQL("PRAGMA synchronous=1"); _db.setTransactionSuccessful(); _db.endTransaction(); } } 

The class of the saved object:

 ... private static InsertHelper ih; public void save(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 good "soared" the code to save 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 to the site

I hope the information will be useful for someone. At once I will say, first of all it is designed for novice programmers. If someone can offer more effective options, I will be glad.

UPD.
In an unconscious burst of joy, I shared my discovery, as it turned out too soon. After the tests, it turned out that in case of an attempt to add some data with another parallel thread, we will get ayay, in addition, some devices swear at attempts to use the pragma directly.

Thus, it remains only to use one transaction (I commented out the error code). In this form, it remains as safe as possible. True, at the same time, it loses all sorts of claims for 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), despite the fact that the younger brothers (HTC Wildfire) did not show a significant increase ...