CODEWITHSUNDEEP
androidsqlite
Eugene
Eugene

Reputation: 60184

Why subsequent queries are faster than the very first one?

I have measured database queries and what I found is that the very first time it runs is slower than all subsequent queries. The very first time I run the code below I got: 11. For all subsequent calls it is always only 1. How does it work so it is faster after being first time queried?

long time = System.currentTimeMillis();
Cursor cursor = DBHelper.getInstance().getAllContacts();
Logger.log(TAG, "measured time is " + String.valueOf(System.currentTimeMillis() - time));

The `getAllContacts() method is:

(getReadableDatabase().rawQuery("SELECT _id, name, title FROM " + CONTACTS_TABLE_NAME, null));

Upvotes: 0

Views: 212

Answers (2)

Donal Fellows
Donal Fellows

Reputation: 137607

There are a few effects that might be at play here.

First, you are also timing the getReadableDatabase() method: if that is doing significant work the first time (like opening the DB file!) and then caching what it does, you'll have a lot of overhead from that. Try just calling that method on its own before the timed zone so that you know that overhead isn't biting.

Secondly, SQLite works by compiling the SQL to an internal bytecode form and then executing that in its own little virtual machine. The compilation step can have non-trivial costs, and is specific to the SQL and the table(s) queried against. Luckily, you're sending the same SQL in every time (that's a constant table name, yes?) and SQLite is smart enough to implement an LRU cache of the compilations so that if you run the same query twice, it's faster. (I don't know the size of the cache; it's probably set at the time you build SQLite, with some sensible default.) You could test this in theory by just querying against a different table each time, but that would be silly. Closing the connection to the DB would also get rid of this cache, but would guarantee that you get poor performance; you're not supposed to measure just the start of things as then you're bearing lots of extra costs for no good reason.

Thirdly, it's quite possible that SQLite only reads some information about the DB and its tables when it actually needs to, so if this is the first query against that table, you may be bearing a number of extra costs. Try doing a different — otherwise never used — query against the table first, before the timing run. (Also, be aware of my caveats in the previous paragraph.)

Fourthly, the ORM layer might be caching the results, but frankly this is actually pretty unlikely as that's rather hard to get right (the hard part is working out when to flush the query results cache due to the DB being updated, and it's actually easier to just not bother with such complexity).

Upvotes: 1

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11161

First time you call an SQL statement, SQLite engine has to prepare that typed SQL statement, ie, convert human readable text in machine executable code, which represents a slightly overhead.

Upvotes: 0

Related Questions