Faster way to insert into SQLite from CSV raw?

Question

CSV raw file size is 11.6 MB.

Here is the code I do with Apache CSVReader & SQLCipher.

Here is the function to read string from raw.

private String readRawText(int rawId) {
        InputStream inputStream = cxt.getResources().openRawResource(rawId);
        InputStreamReader inputreader = new InputStreamReader(inputStream);
        BufferedReader bufferedreader = new BufferedReader(inputreader);
        String line;
        StringBuilder stringBuilder = new StringBuilder();
        try {
            while ((line = bufferedreader.readLine()) != null) {
                stringBuilder.append(line);
                stringBuilder.append('\n');
            }
        } catch (IOException e) {
            e.printStackTrace();
            return null;
        }
        return stringBuilder.toString();
    }

Here is the function to insert data from strings read from readRawText(int rawId):

public void insertDefaultBible() {

        StringReader str = new StringReader(readRawText(R.raw.kjv));

        SQLiteDatabase.loadLibs(cxt);
        File databaseFile = new File(Values.database.file);
        SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(
                databaseFile, "test123", null);

        Iterable<CSVRecord> records = null;
        try {
            records = CSVFormat.EXCEL.parse(str);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }


        for (CSVRecord record : records) {
            ContentValues content = new ContentValues();
            content.put("scripture_code", record.get(1));
            content.put("chapter_number", record.get(2));
            content.put("verse_number", record.get(3));
            content.put("content", record.get(4));
            database.insert("kjv", null, content);
        }

    }

It takes several minutes. How can I make it faster to insert? What makes it slow? Why make it slow?

your csv is comma separated file??? – Anil Jadhav Jul 10 '14 at 02:02 — Anil Jadhav, Jul 10 '14 at 02:02

score 4 · Accepted Answer · answered Jul 10 '14 at 03:14

First, you should interleave the read/write process as @Anil suggests. Not having the intermediate structures will help both from a performance and a memory standpoint.

One other important optimization you should do, however, is to include all the insertions in a single database transaction. SQLite has implicit transactions, which means a loop like this one is doing something akin to:

for (record : records)
{
    begin_transaction();
    insert();
    commit_transaction();
}

It should be much faster (in our case, at least a 3x improvement) if you declare an explicit transaction and then finish it when all records have been processed.

database.beginTransaction();
try
{
    for (record : records)
        database.insert(...);

    database.setTransactionSuccessful();
}
finally
{
    database.endTransaction();
}

this cut my process time from 1 minute per 10k rows of data to 3-5 seconds per 10k rows of data, and they are fairly hefty rows. +1 for a solution that stands the test of time (pun intended) : ) — Corey, May 15 '17 at 17:47

Anil Jadhav · Answer 2 · 2014-07-10T02:28:00.017

your are using two seperate method

first reads all data load into a String
second reads string splits it line by line and then insert data

This process takes time. So instead of this you read csv file one record and insert it directly to database. This process saves process time, memory usage.

Solution code:

 // This method read and writes csv data in one step.
private　boolean readRawTextAndInsert(int rawId) {
      InputStream inputStream = cxt.getResources().openRawResource(rawId);
    InputStreamReader inputreader = new InputStreamReader(inputStream);
    BufferedReader bufferedreader = new BufferedReader(inputreader);
    String line;
    StringTokenizer st = null;
    StringBuilder stringBuilder = new StringBuilder();
    try {
      SQLiteDatabase.loadLibs(cxt);
      File databaseFile = new File(Values.database.file);
      SQLiteDatabase database = SQLiteDatabase.openOrCreateDatabase(
      databaseFile, "test123", null);


        while ((line = bufferedreader.readLine()) != null) {
             st = new StringTokenizer(line, ",");
       　　 ContentValues content = new ContentValues();
        　   content.put("scripture_code", st.nextToken());
            content.put("chapter_number", st.nextToken());
            content.put("verse_number", st.nextToken());
            content.put("content", st.nextToken());
            database.insert("kjv", null, content);
        }
    } catch (IOException e) {
        e.printStackTrace();
        return null;
    }
    return true;
  }

If your file separator is not comma(,) then change only StringTokenizer second parameter with your csv file used separator. — Anil Jadhav, Jul 10 '14 at 02:20

Faster way to insert into SQLite from CSV raw?

2 Answers2