Poor SQLite implementation? First time data access way too slow

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

  开发者_JAVA技巧

  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?

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

Besides still being a bit slower than on PC, the problem seems to be the handling of unindexed fields.

While the configuration in my original post was about 120 times slower, I tried the same with a full indexed database (that means indexes und primary keys everywhere where needed) and the query which now takes 9 ms on the PC is now "only" 8 times slower: execution time about 75 ms.

This of course is still a pretty disappointed result, because i don't want to have to use indexes everytime, even on really small tables.