Inserting 1000000 Rows In Sqlite3 Database

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 th

Solution 1:

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.

classSimpleHelperextendsSQLiteOpenHelper {
    // InsertHelpers are a really good idea - they format a prepared statement// for you automatically.
    InsertHelper mInsert;
    publicSimpleHelper(Context context) {
        super(context, "tanika.db", null, 1);
    }
    @OverridepublicvoidonOpen(SQLiteDatabase db) {
        super.onOpen(db);
        mInsert = newInsertHelper(db, "target");
    }
    @OverridepublicvoidonCreate(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" +
                ")");
    }
    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    }
}

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

longtest(finalint n) {
    longstarted= System.currentTimeMillis();
    ContentValuesvalues=newContentValues();

    for (inti=0; i < n; i++) {
        values.clear();
        // Every 20th insert, generate a conflict in val4Stringval4= 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.

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

classTest2extendsAsyncTask<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
    protectedvoidonPostExecute(Long result) {
        System.out.println(getClass().getSimpleName() + " finished in " + result + "ms");
    }
}

Everything is started like this:

@OverridepublicvoidonCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    mHelper = newSimpleHelper(this);
    mHelper.getWritableDatabase(); // Forces the helper to initialize.newTest1().execute(2055);
    newTest2().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.

Solution 2:

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:

   publicclassMyDatabaseHelperextendsSQLiteOpenHelper {
   
       /*
           Other SQLiteOpenHelper declarations here ...
       */privatestaticfinalStringDATABASE_NAME="application.db";
       privatestaticfinalStringDB_PATH="/data/data/" + context.getPackageName() + "/databases/";
   
   
       /*
           Your SQLiteOpenHelper functions/procedures here ...
       */publicbooleanisDataBaseExist() {
   
           FiledbFile=newFile(DB_PATH + DATABASE_NAME);
           return dbFile.exists();
       }
   
       publicvoidcopyDataBase(Context context)throws IOException {
   
           this.getReadableDatabase();
   
           InputStreaminFile= context.getResources().getAssets().open(DATABASE_NAME);
   
           // Path to the just created empty dbStringoutFileName= DB_PATH + DATABASE_NAME;
           OutputStreamoutFile=newFileOutputStream(outFileName);
   
           // transfer bytes from the inputfile to the outputfilebyte[] buffer = newbyte[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.

Solution 3:

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