Android SQLite much slower than HTML5 WebSql

Question

Two database apps, one HTML5 WebSql and one Android built with ADT. Both use exactly the same Sqlite database so same schemas, tables and indexes.

SELECT
    road.description,
    xy.lat AS lat,
    xy.lon AS lon,
    road.id_road AS id_road
FROM xy
INNER JOIN road
  ON xy.id_road = road.ID_ROAD
WHERE xy.lat BETWEEN -36.89804010977648
                 AND -36.878040110223516
  AND xy.lon BETWEEN 174.78966425022352
                 AND 174.8096642497765

The web app uses db.transaction. The Android SQL runs in a class that extends SQLiteOpenHelper with the database object created in an ASync process.

SQLiteDatabase dbr = this.getReadableDatabase();
Cursor cursor = dbr.rawQuery(sql, null);
if( cursor.moveToFirst()){
    // loop through and analyse approx 400 results
}

Testing on HTC-1V hardware the HTML5 runs the above query in 250ms, on the Android app is takes 4000ms just to open the cursor. Is it possible to get WebSql performance from the Android app ?

What are you doing your benchmarking with? 4000ms just to get a readable database, run a little query and get a cursor is a tad absurd. I've never experienced that. On a dataset of a few thousand records, I've never seen a noticeable slowdown of the app. — jlindenbaum
– jlindenbaum, Commented Feb 27, 2013 at 0:10
@jlindenbaum, hi it's a 35mb database, the xy table has 264k rows. for the benchmarking we compared start and finish Date.getTime(). also ran the query in a loop and calculated the average time. — ajayel
– ajayel, Commented Feb 27, 2013 at 0:20
@Stéphane it's on the HTC 1V hardware which is a recent smartphone — ajayel
– ajayel, Commented Feb 27, 2013 at 20:29

Community · Accepted Answer · 2017-05-23 12:03:21Z

0

It is difficult to say exactly why, but I can tell you what indexes you should create to make it work faster (unless you have those indexes already):

CREATE INDEX road__id_road_idx       ON road (id_road);
CREATE INDEX xy__id_road_lat_lon_idx ON xy   (id_road, lat, lon);

Also, please see this answer on how to optimize your queries on Android, but basic idea is to run your query under EXPLAIN QUERY PLAN and try to eliminate expensive scans.

edited May 23, 2017 at 12:03

CommunityBot

11 silver badge

answered Feb 27, 2013 at 5:42

mvp

118k15 gold badges132 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ajayel Over a year ago

Thanks but the indexes are the same on both databases and double checked using EXPLAIN. Also tried the .setMaxSqlCacheSize() method described in the link which looked promising but didn't change the result.

Collectives™ on Stack Overflow

Android SQLite much slower than HTML5 WebSql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related