page67
49
The most important lessons from Table 3-1 are to know your data volumes and to
size your warehouse accordingly. i will discuss costs later.
I will return to warehouse tuning later in this book as there is much more to unpack
and the subject deserves a chapter to itself.
You must clear out the cache before rerunning or your performance figures will be
incorrect. To do this, you set your session to ignore cached results causing every SQL
statement to be executed.
ALTER SESSION SET use_cached_result = FALSE;
You can find further information on disabling cached results at
https://docs.
snowflake.com/en/sql-reference/parameters#use-cached-result
.
You can find supplemental information at
https://docs.snowflake.com/en/user-
guide/querying-persisted-results
.
Disabling cached results is for performance tuning only.
*** Do not disable cached results in your production code. ***
Then declare your chosen warehouse.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Warehouse declaration does not clear out the warehouse cache, so you must
suspend and restart your warehouse, which also aborts all active SQL statements.
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs ) SUSPEND;
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs ) RESUME;
*** Warehouse suspension is for performance tuning only. ***
Now execute your baseline table creation statements and set the warehouse once for
each statement group according to the optimal size shown in Table 3-1.
ChapTer 3
The Query profiler