page100
82
What Is Long Execution Time?
Long execution time occurs after a query has been compiled and relates to the physical
amount of time required to return a result set. You can use the query history total
elapsed time as the sole indicator of query execution time.
Identifying Long Execution Time
In a production system, you might not immediately identify queries suffering from long
execution time; individual query runtimes may be hidden within complex data pipelines
where multiple SQL statements are executed sequentially. The presenting symptoms
may be hidden within the overall runtime of a process or the backup of files awaiting
processing. Conversely, for queries identified as long running, you need to identify
which data pipeline or process they belong to before remediation can occur.
In this section, you are looking to identify long-running queries from all of the
queries that have been executed.
Let’s first create a table to hold all candidate query IDs for later investigation. I leave
it to you to add timestamps, etc.
CREATE OR REPLACE TABLE long_execution_time
(
query_id STRING,
warehouse_name STRING,
warehouse_size STRING,
query_execution_time_ms NUMBER,
partitions_scanned NUMBER,
partitions_total NUMBER
);
The latency for
QUERY_HISTORY
may be up to 45 minutes.
You can now insert candidate records from
QUERY_HISTORY
; in this example you want
records only for the past seven days; you may want to change the predicates to suit your
requirements.
ChapTer 3
The Query profiler