Speed up Large Data Insertion in Sqlite Database in Android Application

Speed up large data insertion in sqlite database in android application

In my android project I need to insert large data in sqlite database. Inserting 20000 lines of data using ContentValues is not a good idea in term of speed of insertion. It's take more than a minute.



I take a research on how it will be speed up if there's a way. After an hour I found a way.

Here is the code snippet to speed up data insertion in sqlite database in android application.

1. DbHelper.java

 class DbHelper extends SQLiteOpenHelper {
  final static String DATABASE_NAME = "my_database";
  final static int DB_VERSION = 1;
  
  final static String TABLE_NAME = "products";
  final static String PRODUCT_NAME = "product_name";
  final static String PRODUCT_CODE = "product_code";
  final static String PRODUCT_PRICE = "price";
  
  DbHelper(Context context) {
  super(context, DATABASE_NAME, null, DB_VERSION);
  // TODO Auto-generated constructor stub
 }

 @Override
 public void onCreate(SQLiteDatabase db) {
  
  String createTblQuery = "CREATE TABLE IF NOT EXISTS "+TABLE_NAME+
    "(" +
    PRODUCT_NAME +" TEXT, " +
    PRODUCT_CODE +" TEXT,"+
    PRODUCT_PRICE +" DOUBLE"+
    ")";
  // create table
  db.execSQL(createTblQuery);
 }

 @Override
 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
  // TODO Auto-generated method stub
  
 }

}

2. MainActivity.java and DataInserter.java

public class MainActivity extends Activity implements OnClickListener {
 
 final static int NORMAL_INSERT_BTN_ID = 1;
 final static int PAST_INSERT_BTN_ID = 2;
 
 LinearLayout linearLayout;
 Button btn;
 Button pastInsertBtn;
 TextView normalInsertStatusTextView;
 TextView pastInsertStatusTextView;
 ProgressDialog progress;
 
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        this.init();
    }
    private void init(){
     this.linearLayout = new LinearLayout(this);
     this.linearLayout.setOrientation(LinearLayout.VERTICAL);
     
     this.btn = new Button(this);
     this.btn.setOnClickListener(this);
     this.btn.setId(NORMAL_INSERT_BTN_ID);
     this.btn.setText("Normal Insert");
     
     this.pastInsertBtn = new Button(this);
     this.pastInsertBtn.setText("Past Insert");
     this.pastInsertBtn.setOnClickListener(this);
     this.pastInsertBtn.setId(PAST_INSERT_BTN_ID);
     
     this.normalInsertStatusTextView = new TextView(this);
     this.pastInsertStatusTextView = new TextView(this);
     
     this.progress = new ProgressDialog(this);
     this.progress.setMessage("Please wait inserting data");
     this.progress.setCancelable(false);
     
     this.linearLayout.addView(this.btn);
     this.linearLayout.addView(this.pastInsertBtn);
     this.linearLayout.addView(this.normalInsertStatusTextView);
     this.linearLayout.addView(this.pastInsertStatusTextView);
     
     
     this.setContentView(this.linearLayout);
    }
    @Override
 public void onClick(View v) {
  
     int id = v.getId();
     if(id == NORMAL_INSERT_BTN_ID){
      
      new DataInserter(id).execute();
      
     }else if(id == PAST_INSERT_BTN_ID){

      new DataInserter(id).execute();
      
     }
     
  
 }
    
    protected JSONArray testData() throws JSONException{
     JSONArray products = new JSONArray();
     
     for(int x = 0 ; x < 20000; x++){
      JSONObject product = new JSONObject();
      product.put(DbHelper.PRODUCT_NAME, "product name "+x);
      product.put(DbHelper.PRODUCT_CODE, "product code"+x);
      product.put(DbHelper.PRODUCT_PRICE, x);
      
      products.put(product);
      
     }
     
     return products;
    }
    /**
     * 
     * @author 
     *
     */
    class DataInserter extends AsyncTask<String, Object, Boolean>{
     int isPastInsert = 0;
     
     DataInserter(int isPastInsert){
      
      this.isPastInsert = isPastInsert;
      
      progress.show();
     }
     
  @Override
  protected Boolean doInBackground(String... params) {
   // TODO Auto-generated method stub
   
   try {
    
    JSONArray products = MainActivity.this.testData();
    
    
    
    if(isPastInsert == PAST_INSERT_BTN_ID){
     
     long timeStarted = System.currentTimeMillis();
     
     this.pastInsert(products);
     
     long timeEnd  = System.currentTimeMillis();
     
     this.publishProgress(timeStarted,timeEnd);
     
    }else{
     
     long timeStarted = System.currentTimeMillis();
     
     this.normalInsert(products);
     
     long timeEnd  = System.currentTimeMillis();
     
     this.publishProgress(timeStarted,timeEnd);
    }
    
    
    
    
    
    
   } catch (JSONException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
   }
   return null;
  }
  
  @Override
  protected void onProgressUpdate(Object... values) {
   if(isPastInsert == PAST_INSERT_BTN_ID){
    
    long timeStarted = (Long) values[0];
    long timeEnd  = (Long) values[1];
    
    pastInsertStatusTextView.setText("Past Insert Status :\n" +
      "Start at : "+timeStarted+"\n" +
      "End at : "+timeEnd+"\n" +
      "Time elapsed in milliseconds : "+(timeEnd - timeStarted)+"\n" +
      "Time elapsed in seconds : "+(timeEnd - timeStarted)/1000.0+"\n");
    
    
    
   }else{
    
    long timeStarted = (Long) values[0];
    long timeEnd  = (Long) values[1];
    
    normalInsertStatusTextView.setText("Normal Insert Status :\n" +
      "Start at : "+timeStarted+"\n" +
      "End at : "+timeEnd+"\n" +
      "Time elapsed in milliseconds : "+(timeEnd - timeStarted)+"\n" +
      "Time elapsed in seconds : "+(timeEnd - timeStarted)/1000.0+"\n");
   }
  }
  /**
   * 
   * @param products
   * @throws JSONException
   */
  private void normalInsert(JSONArray products) throws JSONException{
   
   DbHelper dbHelper = new DbHelper(MainActivity.this);
   SQLiteDatabase db = dbHelper.getWritableDatabase();
   
   int dbProductCount = products.length();
   
   for(int x = 0 ; x < dbProductCount ; x++){
    
    JSONObject product = products.getJSONObject(x);
    
    ContentValues contentValues = new ContentValues();
    contentValues.put(DbHelper.PRODUCT_NAME, product.getString(DbHelper.PRODUCT_NAME));
    contentValues.put(DbHelper.PRODUCT_CODE, product.getString(DbHelper.PRODUCT_CODE));
    contentValues.put(DbHelper.PRODUCT_PRICE, product.getString(DbHelper.PRODUCT_PRICE));
    
    db.insert(DbHelper.TABLE_NAME, null, contentValues);
   }
   
  
            db.close();
  }
  
  private void pastInsert(JSONArray products) throws JSONException{
   String sql = "INSERT OR REPLACE INTO "
     + DbHelper.TABLE_NAME
     + " ("+DbHelper.PRODUCT_NAME+","+DbHelper.PRODUCT_CODE+","+DbHelper.PRODUCT_PRICE+" ) VALUES (?, ?, ?)";
   
   DbHelper dbHelper = new DbHelper(MainActivity.this);
   SQLiteDatabase db = dbHelper.getWritableDatabase();
   
   int dbProductCount = products.length();
    // begin transactions
            db.beginTransaction();
   SQLiteStatement stmt = db.compileStatement(sql);
   
   for(int x = 0 ; x < dbProductCount ; x++){
    
    JSONObject product = products.getJSONObject(x);
    
    stmt.bindString(1, product.getString(DbHelper.PRODUCT_NAME));
    stmt.bindString(2, product.getString(DbHelper.PRODUCT_CODE));
    stmt.bindString(3, product.getString(DbHelper.PRODUCT_PRICE));
    stmt.executeInsert();
    stmt.clearBindings();
   
   }
   
   // set transactions successful
   db.setTransactionSuccessful();
   // end transaction
            db.endTransaction();
            db.close();
  }

  @Override
  protected void onPostExecute(Boolean result) {
   
   progress.dismiss();
  }
     
  
    }

 
  
}

Download source code here.

See my other blog.

1.  Android Upload File To Server With Data.
2. Android Scrolling Table with Fixed Header Column, Fixed Row , Header Spans and Pagination.
3. Android Calling Javascript function from Java function and Javascript function to Java function in Webview.
4. Android Use Sqlite Database Example
5. Android LinearLayout Example