4

I'm using SQLiteOpenHelper in my Android app and would like to insert ~20k statements in the onCreate method. I can't use a prefilled database as then I'm facing problems with OnePlus devices (SQLiteAssetHelper - problems on specific phones, e.g. OnePlus).

Now I stored my insert-statemtents in a file and use import it the following way: (R.raw.prefilled_data is the file containing the 20k statements).

Currently it takes ~ 6 seconds on a Nexus 5, which is not too bad, but maybe there's a way to make this even faster?

this happens inside onCreate

    try {
        insertFromFile(R.raw.prefilled_data);
    } catch (IOException e) {
        e.printStackTrace();
    }

.

    private void insertFromFile(int resourceId) throws IOException {
        // Open the resource
        InputStream insertsStream = AboalarmApp.getContext().getResources().openRawResource(resourceId);
        BufferedReader insertReader = new BufferedReader(new InputStreamReader(insertsStream));

        // Iterate through lines (assuming each insert has its own line and theres no other stuff)
        while (insertReader.ready()) {
            String insertStmt = insertReader.readLine();
            myDataBase.execSQL(insertStmt);
        }
        insertReader.close();
    }
Community
  • 1
  • 1
swalkner
  • 16,679
  • 31
  • 123
  • 210
  • "I can't use a prefilled database as then I'm facing problems with OnePlus devices ()." -- using [`SQLiteAssetHelper`](https://github.com/jgilfelt/android-sqlite-asset-helper)? What's the problem, specifically? – CommonsWare Jan 14 '16 at 15:26
  • 2
    Use `Sqlite Transaction` to make it more faster. see [Android Database Transaction](http://stackoverflow.com/questions/8147440/android-database-transaction) for more help. if you use `Sqlite Transaction` then it will reduce `6 seconds` to `1 seconds` probably – ρяσѕρєя K Jan 14 '16 at 15:27
  • 1
    Calling sqlite methods in main thread is a bad idea – wilddev Jan 14 '16 at 15:27
  • @CommonsWare sorry, I forgot the link, added it now – swalkner Jan 14 '16 at 15:29
  • @wilddev yeah, but putting it in the background doesn't make it faster, right? – swalkner Jan 14 '16 at 15:29
  • @ρяσѕρєяK doesn't ```onCreate``` and ```onUpgrade``` happen in transactions? I tried adding them by myself, no difference – swalkner Jan 14 '16 at 15:31
  • @swalkner: wrap `myDataBase.execSQL(insertStmt);` line in `transactions ` which will execute faster – ρяσѕρєя K Jan 14 '16 at 15:33
  • @ρяσѕρєяK: `onCreate()` and `onUpgrade()` are already in transactions. – CommonsWare Jan 14 '16 at 15:33
  • @ρяσѕρєяK can you give an example for that? I don't think it makes a difference... – swalkner Jan 14 '16 at 15:34
  • Assuming that we can't get your `SQLiteAssetHelper` problem fixed... 20K inserts in 6 seconds is already a fairly blazing-fast speed, to be honest. The only thing that I can think of to improve matters would be to [precompile an `INSERT` statement](http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#compileStatement%28java.lang.String%29). However, that would increase the parsing time on your side, and so I don't know if it would be a net improvement. – CommonsWare Jan 14 '16 at 15:38
  • The last part of this article sounds interesting: http://blog.teamleadnet.com/2012/03/sqlite-performance-tuning.html – Phantômaxx Jan 14 '16 at 15:47

0 Answers0