Performance SQLite Issue - Can a codechange speed up things?

Question

I use the following code to add rows to my database :

    public void insert(String kern, String woord) {
      SQLiteDatabase db = getWritableDatabase();

      ContentValues values = new ContentValues();
      values.put(KERN, kern);
      values.put(WOORD, woord);

      db.insertOrThrow(TABLE_NAME, null, values);
      return;

Currently, I'm invoking this insert() 3.455 times, to add all words to the database, using : insert("Fruits", "Banana"); It takes forever.

How can I change this code to work faster? I'm thinking in the line of foreach, but don't know how to implement.. Thanks!

/Edit; The solution provided by @hovanessyan works and will do the job. AND.. note that if you have a lot of lines that have to be put in, you might be confronted with the method exceeds max byte limit error. In that case, review the other solution, that suggests packing the database in the actual .APK file.

Answer 1

You can wrap-up those inserts into transaction.

db.beginTransaction();
 try {
  // do all the inserts here

  //method call here, that does 1 insert; For example
  addOneEntry(kern,woord);
  ... 
  db.setTransactionSuccessful();
 } catch (SQLException e) {
         //catch exceptions
 } finally {
   db.endTransaction();
 }


 private void addOneEntry(String kern, String woord) {
   //prepare ContentValues
   //do Insert
 }

Answer 2

You can use bulkInsert:

   ContentValues[] cvArr = new ContentValues[rows.size()];
   int i = 0;
   for (MyObject row : rows) {
    ContentValues values = new ContentValues();
    values.put(KERN, myObject.getKern());
    values.put(WOORD, myObject.getWoord);       
    cvArr[i++] = values;
    }// end for
    resolver.bulkInsert(Tasks.CONTENT_URI, cvArr);

Answer 3

Using the tips of both hovanessyan and Damian (remind me to rep+1 you as soon as I reach 15 ;), I came up with the following solution:

1. For relatively small databases (<1,5Mb)

I created the database using SQLite Database Browser, and put it in my Assets folder.

Then, the following code copies the database to the device, if it's not already there:

    boolean initialiseDatabase = (new File(DB_DESTINATION)).exists();

public void copyDB() throws IOException{ final String DB_DESTINATION = "/data/data/happyworx.nl.Flitswoorden/databases/WoordData.db";

    // Check if the database exists before copying


    Log.d("Database exist", "" + initialiseDatabase);
    Log.d("Base Context", "" + getBaseContext());

    if (initialiseDatabase == false) {

        // Open the .db file in your assets directory
        InputStream is = getBaseContext().getAssets().open("WoordData.db");


        // Copy the database into the destination
        OutputStream os = new FileOutputStream(DB_DESTINATION);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = is.read(buffer)) > 0){
            os.write(buffer, 0, length);
        }
        os.flush();

        os.close();
        is.close();
    }}

In my app, a portion of the database is User-customizable.

I call the code above in onStart() with :

        try {
        copyDB();
    } catch (IOException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

So, when the user presses "reset database to standard" (in preferences screen), I just set the Boolean initialiseDatabase to "false" and wait for the user to go back to the main activity. (thus calling onstart and copying the original database).

I tried to call the Activity.copyDB() from the preferences.java. It's neater, because it doesn't require the user to go back to the main activity to rebuild the database. However, I get an error about not being able to call static references to non-static methods. I don't understand that, but will look into it.