I am trying to compare two queries to understand which is the better and optimized one. Should I look at the 'Elapsed time' or 'Slot time consumed'? Which is a better measure?
Following is an example:
Query 1 - Elapsed time: 0.3 sec. Slot time consumed: 0.100 sec Query 2 - Elapsed time: 0.5 sec, Slot time consumed: 0.081 sec
We need to look at both. First, let's understand what are these.
'elapsed time' is total time taken by BQ to execute your query. 'slot time' is the total time taken by vCPUs to execute your query.
So, 'elapsed time' will tell you how fast your query is executing but 'slot time' will give you, how much of CPU capacity it needed to execute the query.
Ideally 'slot time' should be less than 'elapsed time' as BQ will divide the whole query into multiple stages and execute in different CPUs and the execution will happen in parallel. Then, it will take some time to consolidate the result (if any) and gives the result, so it needs some time to consolidate.
If the table is properly designed, I mean, proper partitioning is done and clustering hierarchy is defined, then the 'elapsed time' will be higher than 'slot time', there shouldn't be huge difference as well.
So, if the 'slot time' is much higher than 'elapsed time' then there is lot of potential to optimize the query and table design as well. Also, GCP will charge on BQ based on how many slots have been used to execute the query. Some links for reference.
https://cloud.google.com/bigquery/query-plan-explanation
