how to find out the execution time of Sqlite query in android

Question

I'm trying to optimize the performance of my Sqlite queries.

I wanted to know if there is any way to find the execution time of each Sqlite statement, or if there is any tool that allows to view the statement execution time in Android SDK?

Although I'm familiar with .timer On and .timer show commands for Query timer.

Example:

Any answer is truly appreciated!

Answer 1

Instead of calculating the execution time by yourself, let sqlite do that for you.
From here:

/**
 * Controls the printing of wall-clock time taken to execute SQL statements
 * as they are executed.
 *
 * Enable using "adb shell setprop log.tag.SQLiteTime VERBOSE".
 */
public static final boolean DEBUG_SQL_TIME =
        Log.isLoggable("SQLiteTime", Log.VERBOSE);

Therefore, to enable execution time tracing run:

adb shell setprop log.tag.SQLiteTime VERBOSE

You will have to restart your application for reloading new settings**. Right afterwards you will start to see these log records in logcat:

02-14 12:27:00.457 11936-12137/osom.info.dbtest I/Database: elapsedTime4Sql|/data/data/osom.info.dbtest/databases/test.db|1.000 ms|UPDATE TestTable SET key=? WHERE _id=1

** Sometimes this won't be enough, so run adb shell stop and adb shell start.

To stop printing these logs either restart the device (this property is not persisted between reboots) or set the property to a higher log level, i.e.:

adb shell setprop log.tag.SQLiteTime ERROR

---

Note for those that are using Jetpack's Room: this solution will work with Room as well (since Room uses Sqlite as underlying DB).

Answer 2

You could do this in Java:

int startTime = System.currentTimeMillis();

... // Execute the query here

int executionTime = System.currentTimeMillis() - startTime; // This variable now contains the time taken by the query, in milliseconds