Android - Where to add PRAGMA synchronous=OFF

Question

I have a code which creates database and insert data into table once when my app installs first time. I want to add "PRAGMA synchronous=OFF" in order to increase speed of insertion. Where in code bellow I should add "PRAGMA synchronous=OFF" ?

public class DataBaseHelper extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "scheduleDB";
    private static final int DB_VERSION = 1;

    Context context;

    public DataBaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DB_VERSION);
        this.context = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        try {                
            db.execSQL("CREATE TABLE time (id INTEGER PRIMARY KEY AUTOINCREMENT, arrival VARCHAR(10), departure VARCHAR(10));");    
            insertTime(db,"city");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        try {
            onCreate(db);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public void insertTime(SQLiteDatabase database, String table) {
        BufferedReader br = null;
        String line;

        if (Build.VERSION.SDK_INT >= Build.VERSION_CODES.HONEYCOMB) {
            database.beginTransactionNonExclusive();
        } else {
            database.beginTransaction();
        }

        try {                
            br = new BufferedReader(new InputStreamReader(context.getAssets().open("time.txt")));
            while ((line = br.readLine()) != null) {                    
                String query = "INSERT INTO "+table+" (arrival, departure) VALUES (?,?)";
                SQLiteStatement statement = database.compileStatement(query);

                String[] time = line.split(",");

                for(int i = 1, n = time.length; i < n; i+=2) { 
                    statement.clearBindings();
                    statement.bindString(1,time[i-1]);//arrival
                    statement.bindString(2,time[i]);//departure
                    statement.execute();
                }
            }
            br.close();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            database.setTransactionSuccessful();
            database.endTransaction();
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

Have you tried some of the other suggestions here for improved insert performance: http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite — Morrison Chang, Jul 12 '16 at 17:26
@Morrison Chang Yes, from there I took transactions and Prepared Statement, but speed still slow for me, thus I want to try to use PRAGMA synchronous=OFF. But where to add it I don't know. — gigs, Jul 12 '16 at 17:30
Related: http://stackoverflow.com/questions/7450437/android-improve-sqlite-performance-optimise, Alternate suggestion use a prebuilt database and include in your APK: http://stackoverflow.com/questions/513084/how-to-ship-an-android-application-with-a-database — Morrison Chang, Jul 12 '16 at 17:37
While I understand _why_ you want to put ``synchronous=OFF`` as pragma, please be aware, that in the rare case of a power loss on the device (battery empty) your database MIGHT become corrupt. So you should use OFF only if your app is capable of recovering from a damaged db (i.e. recreate it). It depends on the importance of the data in the DB whether you should use this. For a log-db it's ok. For purchases (real money) done and other life-important data I would be careful going this path. see https://www.sqlite.org/howtocorrupt.html at point 3.2 — Grisgram, Apr 01 '19 at 08:19

score 0 · Answer 1 · answered Jul 12 '16 at 18:06

0

The correct place to configure the database connection is in the onConfigure() callback.

However, the best method to speed up the creation of the database is to just copy a pre-built file from the assets folder.

answered Jul 12 '16 at 18:06

CL.

173,858
17
217
259

1

I created onConfigure() and added db.execSQL("PRAGMA synchronous=OFF"); to it but it didn't increase speed. – gigs Jul 12 '16 at 19:40

Android - Where to add PRAGMA synchronous=OFF

1 Answers1