Inserting 1000000 rows in sqlite3 database

Question

I want to insert 10,00,000 rows into the database, but the it takes too long time time in insertion like.

e.g. Now I am trying it with 2055 rows and it takes 3 minutes to upload this data into the database.. and this time is too much for 2055 entries.

The following is my method of inserting the data into the database:

      public void insert_database(Context context,String field1,String field2,String field3,String field4,String field5,String field6 ,String field7,String field8,String field9,String field10)
{

    try
    {
        //RayAllen_Database.beginTransaction();
        RayAllen_Database.execSQL(" insert or replace into "+ TableName_csv+" values( '"+field1+"' ,'"+field2+"','"+field3+"','"+field4+"','"+field5+"','"+field6+"','"+field7+"','"+field8+"','"+field9+"','"+field10+"');");


    }
    catch(Exception e)
    {
        //Log.i("Database Exception", "Exception");
        e.printStackTrace();
    }

}

and in the another class called: Parsing Data: here I am parsing the csv file and while parsing:

try {

CSVReader reader=new CSVReader(new FileReader(filename));
String [] nextLine;

//create database
obj.create_database(context);
obj.OpenDatabase(context);
//reader.readNext();

while ((nextLine=reader.readNext())!=null)
{
          //here I am calling the insert_database function
    }
 }

so here It is parsing row one by one and calling the insert method to insert the entry into the database..

But it is too much time taking.. How can I improve the performance of this??

Answer 1

You could populate your database using offline tools and then import it when you install your package. You can either store the database in the external sd card or in the asset folder of your application.

This is how I do it:

1. Copy the application database to a local folder using the Android Debuger Bridge (adb) like this: adb pull /data/data/<your application provider>/databases/yourdatbase.db C:/users/databases/yourdatbase.db.

2. Connect to the SQLites database C:/users/databases/yourdatbase.db with your favourite GUI/CLI tool and complete your population of the 1 000 000 records.

3. Copy your populated database to your Android development environment asset folder.

4. Now uninstall your application from the device to make sure there is no database created when you install for the first time.

5. Modify your SQLiteHepler class so that it checks if a database exists and if one exists it uses that one. If no database exists the Helper copies the one from your asset folder together with your 1 000 000 records. This is how I have done it:

   public class MyDatabaseHelper extends SQLiteOpenHelper {
   
       /*
           Other SQLiteOpenHelper declarations here ...
       */
   
       private static final String DATABASE_NAME   = "application.db";
       private static final String DB_PATH         = "/data/data/" + context.getPackageName() + "/databases/";
   
   
       /*
           Your SQLiteOpenHelper functions/procedures here ...
       */
   
       public boolean isDataBaseExist() {
   
           File dbFile     = new File(DB_PATH + DATABASE_NAME);
           return dbFile.exists();
       }
   
       public void copyDataBase(Context context) throws IOException {
   
           this.getReadableDatabase();
   
           InputStream inFile      = context.getResources().getAssets().open(DATABASE_NAME);
   
           // Path to the just created empty db
           String outFileName      = DB_PATH + DATABASE_NAME;
           OutputStream outFile    = new FileOutputStream(outFileName);
   
           // transfer bytes from the inputfile to the outputfile
           byte[] buffer = new byte[1024];
   
           int length;
   
           while ((length = inFile.read(buffer)) > 0) {
   
               outFile.write(buffer, 0, length);
   
           }
   
           // Close the streams
           outFile.flush();
           outFile.close();
           inFile.close();
       } 
   

This database will be compliled with you app and on first launch all the data will be there. There could be a simpler method, but I hope this helps someone.

Answer 2

Quick example time why you should do the right thing instead of "wrong". This was tested running on ICS 4.0.4, which has horrible INSERT-performance.

First, a simple SQLiteOpenHelper that creates a table with a UNIQUE constraint on a column to cause conflicts now-and-then.

class SimpleHelper extends SQLiteOpenHelper {
    // InsertHelpers are a really good idea - they format a prepared statement
    // for you automatically.
    InsertHelper mInsert;
    public SimpleHelper(Context context) {
        super(context, "tanika.db", null, 1);
    }
    @Override
    public void onOpen(SQLiteDatabase db) {
        super.onOpen(db);
        mInsert = new InsertHelper(db, "target");
    }
    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE target (\n" +
                "_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,\n" +
                "val1 TEXT NOT NULL,\n" +
                "val2 TEXT NOT NULL,\n" +
                "val3 TEXT NOT NULL,\n" +
                // Let's make one unique so we can get some juicy conflicts
                "val4 TEXT NOT NULL UNIQUE\n" +
                ")");
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

Bundled in any old Activity we add the following simple test method:

long test(final int n) {
    long started = System.currentTimeMillis();
    ContentValues values = new ContentValues();

    for (int i = 0; i < n; i++) {
        values.clear();
        // Every 20th insert, generate a conflict in val4
        String val4 = String.valueOf(started + i);
        if (i % 20 == 0) {
            val4 = "conflict";
        }
        values.put("val1", "Value1");
        values.put("val2", "Value2");
        values.put("val3", "Value3");
        values.put("val4", val4);
        mHelper.mInsert.replace(values);
    }
    return System.currentTimeMillis() - started;
}

As you can see, this would cause a conflict every 20th INSERT or so. Calling InsertHelper#replace(..) causes the helper to use a INSERT OR REPLACE on conflicts.

Now, let's run this test code with & without a transaction surrounding it.

class Test1 extends AsyncTask<Integer, Void, Long> {
    @Override
    protected Long doInBackground(Integer... params) {
        return test(params[0]);
    }
    @Override
    protected void onPostExecute(Long result) {
        System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
    }
}

class Test2 extends AsyncTask<Integer, Void, Long> {
    protected Long doInBackground(Integer... params) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        db.beginTransaction();
        long started = System.currentTimeMillis();
        try {
            test(params[0]);
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
        return System.currentTimeMillis() - started;
    }
    @Override
    protected void onPostExecute(Long result) {
        System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
    }
}

Everything is started like this:

@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    mHelper = new SimpleHelper(this);
    mHelper.getWritableDatabase(); // Forces the helper to initialize.
    new Test1().execute(2055);
    new Test2().execute(2055);
}

And the results? Without a transaction the INSERTs take 41072ms. With transactions they take 940ms. In short, FFS, start using InsertHelpers and transactions.

Answer 3

Speeding up sqlite insert operations goes through a similar case and shows how to use transactions to optimize the insertion.