SQLite Forum

.timer on

.timer on

(1) By anonymous on 2020-12-27 16:23:04 [link] [source]

Run Time: real 5982.254 user 259.484375 sys 366.218750

  • What unit of time is used?
  • What do 'real', 'user' and 'sys' mean?

(2.4) By Keith Medcalf (kmedcalf) on 2020-12-27 19:28:32 edited from 2.3 in reply to 1 [link] [source]

The unit is seconds.

The precise definition of real, user, and sys depends on the definition used by the underlying Operating System which is providing these numbers, however generally:

  • real time is the elapsed time as according to the clock on the wall
  • user time is the time spent executing instructions in user mode
  • sys time is the time spent executing instructions in supervisor mode

When the computer is running any particular process the CPU is either in user state (carrying out user computations), sys / privileged / supervisor state (carrying out I/O, for example, on behalf of the user; swapping and context switching; managing memory; and other management work related to the user process), doing something else for someone else, or waiting doing nothing at all. Thus these three figures give you course information about what what was happening.

So, for example, from the numbers you gave one can conclude that when solving the problem that resulted in those figures there was an almost even split between "useful" (user) work and "managerial overhead" (sys), with the amount of overhead work being more than the amount of useful work. However, most of the time nothing at all was being accomplished either because the program was waiting for something (like a slow input such as typing on a keyboard) or was not making progress because some other program was using the CPU and the task being measured was stuck in a queue waiting for service.

(3) By anonymous on 2020-12-27 21:35:14 in reply to 2.4 [link] [source]

Thank you.

I am trying to figure out the corresponding values of a stopwatch elapsed time in my on code with the times reported by the CLI (I understand that .timer is unavailable as an API call).

The elapsed time measured between/including sqlite3_prepare_v2() & sqlite3_step() equates to 'user' (as reported by .timer): Is this correct?

(4) By Simon Slavin (slavin) on 2020-12-27 22:29:10 in reply to 3 [source]

Sorry, no. The shell tool measures time for an entire shell command to be executed. You enter


and the shell tool has to make many API calls. For that specific command there would be one call to sqlite3_prepare_v2(), one or more calls to sqlite3_step(), and one call to sqlite3_finalize(). The output shown because you used .timer ON includes not only the total for all those calls but for the code in the shell tool that processes the command you typed, is executed between those API calls, and converts the output of those calls to the text you see on the console.

This applies to all of 'real', 'user', and 'sys'. They're all totals of everything done between you hitting 'enter' and the shell tool being ready to print another 'sqlite> ' prompt.

(5) By Keith Medcalf (kmedcalf) on 2020-12-27 23:00:02 in reply to 3 [link] [source]

The process times (as output by .timer) user and sys are retrieved with either the getrusage sycall on unix-like systems or the Win32 API GetProcessTimes on Windows.

The real number is obtained from the platform get time of day routine.

Basically the values of all the above are captured at the "start" of the command (when you press enter) and then again just before the prompt is re-issued. The starting times are subtracted from the ending times and displayed as the .timer output which reflects the usage attributed to the processing that occurred between you pressing enter and being ready to process the next command.

This includes such things as output to the display which will often consume a very high amount of real/sys/user time compared to the actual resource usage of the SQL.

Depending on where the output is displayed will determine where this time is accounted. For example, if you are using a native "text console" then it will generally be part of sys and real times because the OS supervisor is "doing the display management" on behalf of the process itself.

On the other hand, if it is being output through X (a different process) the time will be accounted solely to real time since the execution of the display overhead does not occur within the context of either the current process or the OS on behalf of the current process, but rather is accounted as part of the X Windows process and your process will only see the real (elapsed wall clock) results.