insert a lot of data into sqlite in android

Question

currently, I have to insert more than 10 billions data into my android at a time. However, the problem of out of memory will crash the program. The sqlite insert test is quite simple. Just using the for loop to produce the sql insert command and wrqpped by "begin" and "commit".

    private ArrayList<String> sqlInsertCmd = new ArrayList<String>();
    int QUERIES_TIMES = 10000000;
    private void CreateInsertQueries()
    {
        Random localRandom = new Random();
        int i = 0;
        while (i < QUERIES_TIMES)
        {
            int j = localRandom.nextInt(100000);
            sqlInsertCmd.add("insert into " + TABLE + " (a,b,c) values (" + i + "," + 
            j + ",'" + String.valueOf(j) + "')");
            ++i;
        }
    }

    Then..

    mDB.beginTransaction();
    for (int i=0; i<this.QUERIES_TIMES; i++)
    {
        mDB.execSQL(sqlInsertCmd.get(i));
    }
    mDB.setTransactionSuccessful();
    mDB.endTransaction();

Are there any ideas to avoid the out of memory?

Thanks you all, but the code above is just a simple example. In my program, it is more complicated. I must store something in a container(such as hashMap) and build the sql statement dynamically. Could I create 10 services and each service handle 1/10 jobs?

Please visit my [answer](http://stackoverflow.com/a/9141116/996493), This may give you some idea — Lucifer, May 16 '12 at 11:27
if the data is too big, then store data in external memory as a text file and store the path of that text file in database. It will be little slower but better than storing huge amount of data in database — mayank_droid, May 16 '12 at 11:42

score 3 · Answer 1 · edited May 23 '17 at 11:59

A few things:

See my answer here for general tips when doing bulk INSERTs.
There is no need to have a temporary container for your INSERT statements (in this case, the ArrayList<>). Simply use beginTransaction() and endTransaction() in a try...finally.
Utilize pre-compiled statements via SQLiteStatement vs building each INSERT statement as in your example. This is needless thrashing.

Quick and dirty example:

// note: untested code used for illustration!
private boolean bulkInsertData(SQLiteDatabase db, final String tableName) {
    final int NUM_ROWS = 10000000;

    Random random = new Random();

    try {
        SQLiteStatement insStmt = insStmt = db.compileStatement("INSERT INTO " + tableName + " (a, b, c) VALUES (?, ?, ?);");
        db.beginTransaction();
        try {
            for(int i = 0; i < NUM_ROWS; i++) {
                insStmt.bindLong(1, i);
                insStmt.bindLong(2, random.nextInt(100000));
                insStmt.bindString(3, String.valueOf(i));
                insStmt.executeInsert();    //  should really check value here!
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();    
        }
    } catch(SQLException se) {
        return false;
    }

    return true;
}

score 0 · Answer 2 · answered May 16 '12 at 11:59

Instead of creating an ArrayList which contains 10000000 number of sql queries (and might be the potential reason for OutOfMemory), you may simply start inserting records in the same loop where you are creating query strings. For example:

private void CreateInsertQueries(SQLiteDatabase mDB)
{
    Random localRandom = new Random();
    int i = 0;
    String query;

    try{
        mDB.beginTransaction();

        while (i < QUERIES_TIMES)
        {
            int j = localRandom.nextInt(100000);
            query = "insert into " + TABLE + " (a,b,c) values (" + i + "," + 
            j + ",'" + String.valueOf(j) + "')";
            ++i;

            mDB.execSQL(query);
        }

        mDB.setTransactionSuccessful();
    }
    catch (SQLException e) { e.printStackTrace(); }
    finally{ mDB.endTransaction(); }
}

Doing so will help you to skip ArrayList which (i think) is the reason for OutOfMemory due to holding so so many records.

And make sure you do use External Memory to host your database file, otherwise not all phones have bigger internal memory.

insert a lot of data into sqlite in android

2 Answers2