Execution Time of an SQLite Query: Units

Execution time of an SQLite Query: Units

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

sqlite shell reports CPU time: what are the units?

The units are in seconds. See ref: https://github.com/freebsd/pkg/blob/master/external/sqlite/shell.c

snapshot of code from that page:

/* Return the difference of two time_structs in seconds */
static double timeDiff(struct timeval *pStart, struct timeval *pEnd){
  return (pEnd->tv_usec - pStart->tv_usec)*0.000001 + 
         (double)(pEnd->tv_sec - pStart->tv_sec);
}

/*
** Print the timing results.
*/
static void endTimer(void){
  if( enableTimer ){
    struct rusage sEnd;
    getrusage(RUSAGE_SELF, &sEnd);
    printf("CPU Time: user %f sys %f\n",
       timeDiff(&sBegin.ru_utime, &sEnd.ru_utime),
       timeDiff(&sBegin.ru_stime, &sEnd.ru_stime));
  }
}

If you would like to convert to milliseconds, multiply by 1000.

Reducing time of sqlite3 execute/fetchone in python

The execute() and fetchone() calls are where the database does all its work.

To speed up the query, the lookup columns must be indexed. To save space, you can use a clustered index, i.e., make the table a WITHOUT ROWID table.

Get real SQLite run time?

You can use the Unix time command to time anything.

$ time some-command
...
...
normal output
...
...
real    0m0.114s
user    0m0.039s
sys     0m0.065s

So, if you can put your SQL commands in a file that you can pass to sqlite, you can time the entire execution.

Select takes a long time in a 160GB database

The relevant setting is pragma cache_size = 200000; 200000 pages of 4096 bytes. After setting that, for the first time query, it takes approximately 3s and second time query takes 0.28s. Phew.

The cache settings improved the performance for some time. We are working off an AWS linux VM with EBS SSD attached. There seems to be problem in the environment as well. The query times in my Mac is 6.3 times faster than the AWS linux / EBS environment.

---

Related Topics