What is included in "Runtime" in System Activity?

[margaret_abercr]

Knowledge Drop

Last tested: May 11, 2020

The Question:

System Activity users who are trying to assess query performance will often ask things like:

• What is included in query runtime?
• How come the query runtime doesn't match the runtime in my database?

Answer:

Query runtime is the time Looker spends from when the query is asked to when it is returned:

• Sending the query to the DB
• Waiting for a result from the DB
• Receiving the result from the DB and writing the JSON for the visualization to render

  This content is subject to limited support.                

[LawrenceK]

Is there any way to limit runtime to just the time used to run the query?

I was freaking out when Looker was saying runtime was close to four hours for a simple query, then I researched and it says it includes queue time, which I really don’t care about as long as it runs.

[margaret_abercr]

The best way to check how long the query runs on the database is to check directly from the database. If your database supports context comments, such as Big Query, you can find the Looker History ID within the query’s context comments, which should make it easier to track down the query for which you are looking. 

[LawrenceK]

@margaret_abercr , from what I can see, the record “queue” time for a report is 768 minutes (I’m sure execution itself was no more than 10 minutes), and a whole page of 600+ minutes. This does NOT give management warm fuzzies about the product when they see this large of numbers in the scheduled_jobs page.

We use PostGreSQL, so context comments are not available.

[margaret_abercr]

Hi @LawrenceK, 
In that case, I recommend taking the SQL from the query and running it on the database directly to be sure about the execution time. The next step would be for someone from your company (a Looker Admin), to post a product idea in our portal as that is the best way to communicate with the product team what you would like to see in the product. 

As for this being a scheduled job, the next step you can take is using the Instance Performance Dashboard’s (under the Admin section for System Activity) Heatmap to see if these long times are associated with a queue of too many schedules processed at the same time. If so, you might need to space your schedules out throughout the day to avoid the schedule bottleneck and these long execution times, which is slightly different than query run times because it includes delivery of the schedule, and for visual schedules, rendering.