Android - speed up data insertion into database

Question

Currently, I have a CSV file that I am analyzing and trying to insert data into an Android database. The problem I am facing is that it takes too long to insert all the data. This is a good amount of data, but I feel it should not take 20 minutes or so.

Basically, I create my database and then start parsing. When analyzing each individual CSV line, I take the necessary data and insert it into the database. Only about 40,000 lines.

Can this process be accelerated? I tried batch inserts, but it never helped (unless I did it wrong).

The code is below.

Thanks.

DatabaseHelper (I have two insert commands based on the amount of data in each csv line):

// add zipcode public void add9Zipcode(String zip, String city, String state, String lat, String longi, String decom) { // get db and content values SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); db.beginTransaction(); try{ // add the values values.put(KEY_ZIP, zip); values.put(KEY_STATE, state); values.put(KEY_CITY, city); values.put(KEY_LAT, lat); values.put(KEY_LONG, longi); values.put(KEY_DECOM, decom); // execute the statement db.insert(TABLE_NAME, null, values); db.setTransactionSuccessful(); } finally { db.endTransaction(); } db.close(); } public void add12Zipcode(String zip, String city, String state, String lat, String longi, String decom, String tax, String pop, String wages) { // get db and content values SQLiteDatabase db = this.getWritableDatabase(); ContentValues values = new ContentValues(); db.beginTransaction(); try{ // add the values values.put(KEY_ZIP, zip); values.put(KEY_STATE, state); values.put(KEY_CITY, city); values.put(KEY_LAT, lat); values.put(KEY_LONG, longi); values.put(KEY_DECOM, decom); values.put(KEY_TAX, tax); values.put(KEY_POP, pop); values.put(KEY_WAGES, wages); // execute the statement db.insert(TABLE_NAME, null, values); db.setTransactionSuccessful(); } finally{ db.endTransaction(); } db.close(); } 

Analysis File:

 public void parse(ArrayList<String> theArray, DatabaseHandler db) { String[] data = null; // while loop to get split the data into new lines // for loop to split each string in the array list of zipcodes for (int x = 0; x < theArray.size(); x++) { if(x == 10000 || x == 20000 || x == 30000 || x == 40000){ Log.d(TAG, "x is 10k, 20k, 30k, 40k"); } // split string first into an array data = theArray.get(x).split(","); // separate based on the size of the array: 9 or 12 if (data.length == 9) { db.add9Zipcode(data[0], data[2], data[3], data[5], data[6], data[8]); } else if (data.length == 12) { db.add12Zipcode(data[0], data[2], data[3], data[5], data[6], data[8], data[9], data[10], data[11]); /* * theZip.zip = data[0]; theZip.city = data[2]; theZip.state = * data[3]; theZip.lat = data[5]; theZip.longi = data[6]; * theZip.decom = data[8]; theZip. = data[9]; theZip.population * = data[10]; theZip.wages = data[11]; */ } } 

Answer 1

Refer to this answer I made earlier: Insert 1,000,000 rows into sqlite3 database

In short, use InsertHelper and do more than one insert per transaction - if you haven’t done something elusive, the speed increase should be noticeable.

Edit:
Shortly speaking:

• Your SQLiteOpenHelper should be singleton, used throughout the application.
• Do not click close() on your SQLiteDatabase instance - it is cached in SQLiteOpenHelper , and each time you close, you force the helper to reopen it.
• Download your inserts, start the transaction outside the call to the addZipCode methods and mark it successful after you have done all the inserts, and then commit the transaction.
• Use InsertHelper - it will correctly format the insert as a prepared statement and will be pleasant and reusable.
• Remember to synchronize access to the database - if you do not intend to do all the work with the database in a user thread (which is not recommended) - you need to either enable locking or protect access to the database to avoid concurrent access.