I have always been using With tabes when working with long queries but I recently tried the create temp table and the execution was so much faster. The one with With tables (variable names) got an error as too heavy after 12 minutes and the one with create or replace temp table finished in less than 2 minutes. Why is that the case?

CodePudding user response：

Temporary tables give flexibility to make customized tables for data visualization, as per the analytics requirements. More so, the use-case of TEMP is in the local temporary tables, only visible to the current session.

Meanwhile, the WITH clause acts as a temporary table, but it is actually a result of a subquery which can be used somewhere else.

The time difference that you get is because temporary tables use cache query results. This means that the query values are stored in the cache memory. That’s why it is faster to execute than the queries with the WITH clause. Sometimes, when you run a duplicate query, BigQuery attempts to reuse cached results.