CODEWITHSUNDEEP
javaandroiddatabaseperformancesqlite
Vlad Schnakovszki
Vlad Schnakovszki

Reputation: 8601

Android SQLite: Running the Same Query with Different Parameters Multiple Times Efficiently

I have a table in my SQLite database:

Species:

_id  |  species  |  description 
---------------------------
  1  |  Aardvark |  Some description for Aardvark
  2  |  Aardwolf |  Some description for Aardwolf
  3  |  Caracal  |  Some description for Caracal

I receive a list of ids from the server and I want to display only the species corresponding to the ids I received.

Now I am aware of a few options:

1. The obvious and naive way of doing this is something along the lines of:

SQLiteDatabase db = this.openDatabase();
for (int id : idList) {
    Cursor cursorSpecies = db.query(true, TABLE_SPECIES, new String[] {COL_SPECIES_SPECIES},
            COL_ID + "=?", id, null, null, null, null);
    cursorSpecies.moveToNext();
    speciesList.add(cursorSpecies.getString(0));
    cursorSpecies.close();
}

This will execute way too many operations and I assume multiple small "disk" reads, which would be very slow.

2. Another option would be to use an SQLiteStatement but this only returns a single value, which doesn't work for my example and shouldn't really be used for queries anyway.

3. Yet another option would be to manually concatenate the conditions into a raw SQL query, something along the lines of:

SQLiteDatabase db = this.openDatabase();
String query = "SELECT * FROM " + TABLE_SPECIES + " WHERE ";
for (int id : idList) {
    query += COL_ID + "=" + id + " OR ";
}
// I am aware this will end in an " OR" but this is not the point of this example so please ignore it.
Cursor cursorSpecies  = db.rawQuery(query, null);
// Use the cursor and close it.

While this should work decently well, a very large query would probably break some query string length limit so this is not ideal either.

All of these examples would work to a certain extent but they all have pitfalls. Somehow I feel that I am missing a solution to this, hence the question:

What is the proper way of executing this type of query?

Thank you.

Upvotes: 0

Views: 994

Answers (1)

laalto
laalto

Reputation: 152847

For the special case in the question, consider just WHERE id IN (x,y,z, ...).

To address the question in the title and not just the special case in the question body:

The Android SQLite API is not very versatile in this regard.

In raw sqlite3 C API one would do it with a single sqlite3_prepare*() call to obtain a sqlite3_statement and bind the arguments in place, sqlite3_step() to get row(s) and then reset the statement to reuse it with new argument bindings.

In Android API, the statement corresponds to a Cursor and stepping is equivalent of moving the cursor. The reset and rebind functionality is only available in SQLiteCursor as requery() and setSelectionArguments().

So try something along the following lines:

  1. Do a regular query with selection args.

  2. Assuming the default cursor factory, cast the resulting Cursor to a SQLiteCursor.

  3. Access the row(s) you need.

  4. Update the selection args with setSelectionArgs()

  5. requery()

  6. Goto 3 unless done

Upvotes: 1

Related Questions