CODEWITHSUNDEEP
snowflake-cloud-data-platform
Kirby
Kirby

Reputation: 15855

Snowflake Query Profile, Total Duration vs. Total Execution Time

I'm analyzing query performance in Snowflake.

When I look at the Query Profile in the Snowflake web console, like https://xxxxxx.snowflakecomputing.com/console#/monitoring/queries/detail?queryId=xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, I see "Total Execution Time" took 190 milliseconds and I see that "Total Duration" took 2.7 seconds.

I can't figure out why there is such a vast discrepancy between the two numbers. Where can I learn the source of my extra 2.51 seconds?

total duration execution time

I can see in Snowflake documentation that

Execution time provides information about “where the time was spent” during the processing of a query. Time spent can be broken down into the following categories:

Processing — time spent on data processing by the CPU.

Local Disk IO — time when the processing was blocked by local disk access.

Remote Disk IO — time when the processing was blocked by remote disk access.

Network Communication — time when the processing was waiting for the network data transfer.

Synchronization — various synchronization activities between participating processes.

Initialization — time spent setting up the query processing.

https://docs.snowflake.com/en/user-guide/ui-query-history.html

But a lot more "time was spent" outside that execution time and I'm trying to figure out its source.

Upvotes: 1

Views: 4581

Answers (2)

Simon D
Simon D

Reputation: 6269

Have a look at the snowflake.account_usage.query_history table. It will give you a better breakdown of how long things took to run. Here is an example query:

select
    QUERY_TEXT,
    EXECUTION_STATUS,
    START_TIME,
    END_TIME,
    TOTAL_ELAPSED_TIME,
    COMPILATION_TIME,
    EXECUTION_TIME,
    QUEUED_PROVISIONING_TIME,
    QUEUED_OVERLOAD_TIME,
    QUEUED_REPAIR_TIME,
    TRANSACTION_BLOCKED_TIME
from snowflake.ACCOUNT_USAGE.QUERY_HISTORY
limit 10

Upvotes: 3

Dean Flinter
Dean Flinter

Reputation: 674

Execution time is literally the time it took for the query to execute. Total duration time includes things like queuing/waiting for warehouse, reading from disk/memory, compilation of results etc.

Upvotes: 0

Related Questions