page95
77
What Is Long Compilation Time?
Any query can be considered to have a long compilation time where the query
compilation time exceeds the query execution time. In other words, more time is spent
compiling a query than performing real work in executing and delivering the result set.
As you know, compute is expensive. Our objective is to squeeze the maximum
performance from the system. Therefore, understanding the causes of long compilation
time is important, after which you may be able to remediate the root cause.
Sometimes, long compilation time is unavoidable and works as expected.
Identifying Long Compilation Time
In a production system, you might not immediately identify queries suffering from long
compilation time. You are looking to identify these queries from all of those 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_compilation_time
(
query_id STRING,
warehouse_name STRING,
warehouse_size STRING,
compilation_time_ms NUMBER,
execution_time_ms NUMBER,
time_multiple NUMBER //Compilation / Execution
);
latency for
QUERY_HISTORY
may be up to 45 minutes.
ChapTer 3
The Query profiler