Most efficient way to access/write data to SQLite on Android

Question

Currently I'm using ContentProvider in my application. Because of "layers" and no actual need for provider - I'm working on optimizing data access as much as possible. Here is my attempt to do this:

public static String getPreferenceString(Context context, String key)
    {
        DatabaseHelper helper = new DatabaseHelper(context);
        SQLiteDatabase database = helper.getReadableDatabase();
        SQLiteStatement statement = database.compileStatement("SELECT Value FROM Preferences WHERE Key='" + key + "' LIMIT 1");

        try
        {
            return statement.simpleQueryForString();

        }
        catch (Exception ex)
        {
            return "";
        }
        finally
        {
            statement.close();
            database.close();
            helper.close();
        }
    }

    public static void setPreferenceString(Context context, String key, String value)
    {
        DatabaseHelper helper = new DatabaseHelper(context);
        SQLiteDatabase database = helper.getReadableDatabase();
        SQLiteStatement statement = database.compileStatement("INSERT OR REPLACE INTO Preferences (Key, UpdatedOn, Value) VALUES ('" +
                key + "', '" +
                Utility.getDateConvertedToUTCDBString(new Date()) + "', '" +
                value + "'); ");
        try
        {
            statement.execute();
        }
        finally
        {
            statement.close();
            database.close();
            helper.close();
        }
    }

Is that about as close as I can get to direct calls to SQLite?
Should I have all this .close() statements in my code?
In setPreferenceString I did copy/paste and called getReadableDatabase even though I write data and it works. Why?

What do you mean by "direct calls"? AFAIK SQL queries are lowest you can go against a DB. — m0skit0, Feb 14 '12 at 16:36
Yes, that's what I meant. Just wanted to check to make sure I'm doing most direct calls. Also, I wanted to see if this code legit - no locking possibilities, etc — katit, Feb 14 '12 at 16:40
i cant find the code where do you select a database to do all this queries, this done in the extended DatabaseHelper class, didnt it? — Andreas, Feb 14 '12 at 16:41
Yes, I have DatabaseHelper : SQLiteOpenHelper where I take care of creating tables, upgrades, etc — katit, Feb 14 '12 at 16:47
Does Traceview actually indicate you have a performance issue here? — CommonsWare, Feb 14 '12 at 17:06
Not in this spot, thats just small example. But my app completely driven off database and there is pretty big tables involved. Yes, I see and feel slowdowns when data being inserted into large table repeatedly. — katit, Feb 14 '12 at 17:10

m0skit0 · Accepted Answer · 2012-02-14T17:02:06.833

0

Is that about as close as I can get to direct calls to SQLite?

AFAIK SQL queries are closest you can go against RDBs

Should I have all this .close() statements in my code?

Personally, I would not create a DatabaseHelper, an SQLiteDatabase, and an SQLiteStatement each time I call that method. I would create all this just before you need them, and close them when no needed anymore. Also centralizing this is a good idea IMHO (using a singleton, for example).

Also your SQL statement could be written like

SELECT Value FROM Preferences WHERE Key= ? LIMIT 1

This way you only have to prepare it once and bind parameters as you need the statement. Same goes for any SQL query.

edited Feb 14 '12 at 17:02

answered Feb 14 '12 at 16:40

m0skit0

25,268
11
79
127

Would you create Helper/Database/Statements in Application object? I read somewhere that Application object will be more likely killed by OS if I add lot's of stuff to it – katit Feb 14 '12 at 16:42
Another thing.. There is no way to do parameterized SQL with SQLiteStatement in Android unless I'm missing something. I'm using API7 – katit Feb 14 '12 at 16:45
Can you point me to that writing? Also you can use a singleton to store all this and create/destroy on will. I won't put it on that method anyway. You probably have this same code cloned in many places if you access the DB for other things, and that's definitely not good :) – m0skit0 Feb 14 '12 at 16:48
I can't point you - but thats what I read - if application takes more resources it is more likely to be collected when OS needs resources. This code is not reallu "tight" as I want it to, thats why I'm posting here to see how I can avoid doing it. I just did 2 of those for now but there is 100+ to go – katit Feb 14 '12 at 16:50
No problem. That's true, but it can be collected when you're executing that method as well. Also if you use something like a singleton, you can load those and unload when not needed. This will allow any part of the application to use the DB and any common prepared statements without having to create/destroy them on each part of the code you're running. – m0skit0 Feb 14 '12 at 17:00
I can't really prepare statements since SQLiteStatement doesn't support parameters – katit Feb 14 '12 at 17:03
Why you can't use parametrized SQLs? http://stackoverflow.com/questions/433392/how-do-i-use-prepared-statements-in-sqlite-in-android – m0skit0 Feb 14 '12 at 17:04

Most efficient way to access/write data to SQLite on Android

1 Answers1