Poor SQLite implementation? First time data access way too slow

Question

I am new to Android programming, however I am quite used working with SQLite databases.

• My application opens a SQLite3 database on the SD card and runs a relatively complex query (5 joins, 1 subquery, 2 where clauses) using SQLiteDatabase.rawQuery

  public Cursor queryDataBase(String sql, String[] selectionArgs){
      Cursor c = myDB.rawQuery(sql, selectionArgs);
      return c;
  }
  

• The SQL statement is given by a hardcoded String.

• The query returns 585 rows with 24 columns.
• I had to do a trade-off between storage space and indexing, but on all bigger tables (about ~ 40 000 entries, for now) indexes are used, SQLite shows for the query: Steps: 155 , Sorts: 0, AutoIdx: 1077

• I am not using primary keys, thus I also didn't rename anything to "_id".

• The execution of rawQuery is relatively fast, execution time is about 2 ms.

• Accessing this data takes way too much time, e.g. by c.moveToFirst(), execution time is about 1700 ms! (same for Cursor.getRowCount(), or apparently all first time access to the actual result set).
• Doing the same on a PC (2 GHz, 1 GB RAM, SATA2 HDD) with e.g. SQLiteSpy it takes 15 ms to display the result set.
• Doing it on the PC with a C++ Implementation it's also 15 ms up to 30 ms.

So what am I missing here? Is it actually possible that my handset with 800 MHz, 2 GB RAM, MicroSD is about 120 times slower?

Answer 1

The execution of rawQuery is relatively fast, execution time is about 2 ms.

Accessing this data takes way too much time, e.g. by c.moveToFirst(), execution time is about 1700 ms! (same for Cursor.getRowCount(), or apparently all first time access to the actual result set).

That's the key: rawQuery does not actually perform the query, but that is delayed until the first time the query results needs to be use.

Source: http://groups.google.com/group/android-developers/browse_thread/thread/c9277225f8dbc4d1/65de841f284f09e6?lnk=gst