Menu
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.
732
Query one takes about 30ms, but 150ms to fetch the data from the database. Query two takes about 3ms -this is the one I therefore prefer-, but also takes 170ms to fetch the data.
Actually, SQLite will easily do 50,000 or more INSERT statements per second on an average desktop computer. But it will only do a few dozen transactions per second. Transaction speed is limited by the rotational speed of your disk drive.
The SQLite website (https://www.sqlite.org/) uses SQLite itself, of course, and as of this writing (2015) it handles about 400K to 500K HTTP requests per day, about 15-20% of which are dynamic pages touching the database. Dynamic content uses about 200 SQL statements per webpage.
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
