Total process time PROC SQL, understanding the log

Question

I'd like to understand the time log better to improve performance.

My real time is typically much bigger than the cpu time. Is it expected ?

Is the difference due to disk access ? Are the in memory operations included in CPU time or in real time ?

Is there useful information regarding performance optimization in the other lines of the log ?

An example:

NOTE: PROCEDURE SQL used (Total process time):
      real time           9:06.00
      user cpu time       1:36.79
      system cpu time     19.11 seconds
      memory              7463.31k
      OS Memory           24628.00k
      Timestamp           06/07/2018 12:45:31 PM
      Page Faults                       7
      Page Reclaims                     1566
      Page Swaps                        0
      Voluntary Context Switches        370694
      Involuntary Context Switches      36835
      Block Input Operations            0
      Block Output Operations           0

score 2 · Accepted Answer · answered Jun 07 '18 at 14:55

CPU time is how much CPU processing was going on - how many CPU clock cycles were used. This is per core; it can be greater than real time, if multiple cores (virtual or physical) are in use. For example, something that's CPU-intensive and works with parallel processing uses 4 cores on your machine for 2 minutes, real time will be 2 minutes and CPU time could be as high as 8 minutes.

Real time is literally the clock time between job start and job end. In the case of proc sql, it's until quit is reached - so if you leave proc sql open without a quit, the real time could be until you next run a proc.

Much of the time, the difference between CPU time and Real time is disk i/o as you say - if you're reading over a network or from a spinning disk, it's likely that your disk i/o will take longer than your CPU time.

Reading a "file" from memory is not included in CPU time except insofar as it involves the CPU.

The other fields are helpful for diagnosing performance issues at varying levels. Amount of memory used could indicate particular issues with your code (if it's using huge amounts of memory, maybe you're doing something inefficiently).

You might want to read some papers on SQL efficiency and optimization, like Kirk Lafler's - see in particular the _METHOD and _TREE discussion at the end. And of course the various ways to get more information are mentioned in the documentation, such as STIMER and FEEDBACK.

Total process time PROC SQL, understanding the log

1 Answers1