CODEWITHSUNDEEP
linuxsqlite
Spade
Spade

Reputation: 2280

Execution time of an SQLite Query: Units

As described in SQLite Documentation one could use:

sqlite> .timer ON

or add the same command to ~/.sqliterc

When this is done, the SQLite shell responds with user and sys components of CPU Time for every query executed:

user@machine% sqlite3 test.db
-- Loading resources from ~/.sqliterc

SQLite version 3.7.14 2012-09-03 15:42:36
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

sqlite> select count(*) from my_table;
count(*)
10143270
CPU Time: user 0.199970 sys 1.060838

While I found this answer providing evidence for the units of time being in seconds, I am having a hard time agreeing with it. When timing the execution of a query using a stopwatch, I find almost every query to be taking longer than what the shell times it as taking. For example, the query timed in the above example approximately took a little over 1 minute and 54 seconds in real time. What is the reason for this discrepancy?

So once again, what are the units? What are user and sys components?

I am running SQLite 3.7.14 on a Debian GNU/Linux 6.0.6 (squeeze) distribution accessible over NFS.

Upvotes: 37

Views: 31018

Answers (1)

CL.
CL.

Reputation: 180070

user is the time that the CPU spent executing code in user space (i.e., in the database itself); sys is for code in the kernel.

When doing I/O, the CPU spends most of the time waiting.

Sufficiently recent versions of SQLite also show the elapsed real time:

SQLite version 3.8.6 2014-08-15 11:46:33
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .timer on
sqlite> UPDATE ...;
Run Time: real 36.591 user 17.362911 sys 1.809612

Upvotes: 37

Related Questions