Android Improve SQLite Performance Optimise

I have an onCreate that currently opens an sqlite database and reads off 4 values. I then have a conditional and depending on what activity has sent it there it either displays those values or updates two values and then displays the other.

Now if I run this activity without updating the database it is lightning fast. Whereas if I run two queries to write to the database it can be sluggish. Is there anything I am able to do to optimise this.

The problem is the display stays on the previous activity until the sqlite updating has completed. This seems to be the problem.

Sorry for what is most likely a rubbish explanation. Please feel free to ask me to better describe anything.

Any help appreciated.

    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
    setContentView(R.layout.reason);

  //Opens DB connection

    type = b.getString("TYPE");


    get();
    if(type.equals("next")){ update();}
    db.close();
    }


    public void get(){      
Cursor b = db.rawQuery("SELECT * FROM " +
        DB_TABLE2 +" WHERE _id='1'" , null);
b.moveToFirst()开发者_JS百科;
id = b.getInt(b.getColumnIndex("nextq"));
    nextvalue = b.getInt(b.getColumnIndex(type));
if(nextvalue==0){nextvalue=1;}
b.close();           
nextvalue ++;
    }

    public void update(){   
db.execSQL("UPDATE "
        + DB_TABLE2
        + " SET nextq='" + nextvalue + "'"
        + " WHERE _id='1'");
db.execSQL("UPDATE "
        + DB_TABLE
        + " SET answered_correctly='" + anscorrect +"' , open ='1' WHERE _id='" + id + "'");
    }

Enclose all of your updates inside a single transaction. Not only is it better from a data integrity point of view, but it's also much faster.

So, put a db.beginTransaction() at the start of your update(), and a db.setTransactionSuccessful() followed by db.endTransaction() at the end.

You can do something like this, but be warned, the Pragma Syncronize setting can be dangerous, as it turns off security features in Sqlite. Having said that, it increased my recording to roughly 0.5ms per row, or going from 350ms down to 15-20, and for another table, going from 5000-9000ms down to roughly 300.

// this cut down Insert time from 250-600ms to 14-30 ms.
    // with prgma syncronous set to off, this drops it down to 0.5ms/row for membernames
    final InsertHelper ih = new InsertHelper(database, SQLiteHelper.TABLE_MEMBERS);
    final int nameColumn = ih.getColumnIndex(SQLiteHelper.MEMBER_TABLE_MEMBERNAME);
    final long startTime = System.currentTimeMillis();

    try {
        database.execSQL("PRAGMA synchronous=OFF");
        database.setLockingEnabled(false);
        database.beginTransaction();
        for (int i = 0; i < Members.size(); i++) {
            ih.prepareForInsert();

            ih.bind(nameColumn, Members.get(i));

            ih.execute();
        }
        database.setTransactionSuccessful();
    } finally {
        database.endTransaction();
        database.setLockingEnabled(true);
        database.execSQL("PRAGMA synchronous=NORMAL");
        ih.close();
        if (Globals.ENABLE_LOGGING) {
            final long endtime = System.currentTimeMillis();
            Log.i("Time to insert Members: ", String.valueOf(endtime - startTime));
        }
    }

the main things you want are the InsertHelper, the "SetLockingEnabled" features, and the "execSQL Pragma...". Keep in mind as I said that using both of those can potentially cause DB corruption if you experience a power outage on your phone, but can speed up DB inserts greatly. I learned about this from here: http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/#comment-2685

You can also ignore my logging stuff, I had it in there to do some sort of benchmarking to see how fast things took.

Edit: To explain briefly what those options do, I'm basically disabling security and integrity features in SQLite in order to basically pipe data into the database. Since this occurs so fast (around 14-20ms on average now), the risk is acceptable. If this was taking seconds to occur, I wouldn't risk it, because in the event something happens, you could get a corrupted DB. The Syncronize Option is the greatest risk of all, so judge if you want to take that risk with your data. I would recommend using timing features like I've included, to see how long it takes to insert data into your db each time you try something, then determine what level of risk you want. Even if you don't use those two, the other features (InsertHelper and the BeginTransaction stuff) are going to help improve your database work greatly.

Either create a new thread for the database to run on and have a callback for UI update, or if the UI is not dependent on the database change just create the new thread. Executing database stuff on the UI thread will always slow down the UI responsiveness a bit. Check out AsyncTasks or just create a new thread if the UI doesn't need a callback on complete.

Just be careful to not get too careless with thread creation :)