page66
48
Having previously set the environment, let’s copy the tables across. Wherever
possible, I use self-generating SQL. Note the addition of the
_baseline
suffix as you will
be creating more objects later.
SELECT 'CREATE OR REPLACE TABLE '||
LOWER ( table_name )||'_baseline'||
' AS SELECT * FROM snowflake_sample_data.tpch_sf1000.'||
LOWER ( table_name )||';'
FROM snowflake_sample_data.information_schema.tables
WHERE table_schema = 'TPCH_SF1000';
Remember that we are using an X-Small warehouse; therefore, runtimes will be
considerable. Cut and paste the generated output back into SnowSight.
Before executing the generated code and because this is a book on performance
tuning, let’s look at runtimes for different size warehouses. To save you the expense
of running incorrectly sized warehouses, I have run Create Table As SELECT (CTAS)
benchmarks using code generated from the previous query. The timings shown in
Table 3-1 are in seconds. Note there may be some small variances in your runtimes
should you choose to repeat the tests.
Table 3-1.
TPC Baseline Table Copy Times and Data Volumes
Table/Warehouse
X-Small
Small
Medium
Large
X-Large
Row Count
customer_baseline
80
44
25
15
11
150000000
lineitem_baseline
2195
1099
561
296
165
5999989709
nation_baseline
1
1
1
1
1
25
partsupp_baseline
291
151
79
44
25
800000000
region_baseline
1
1
1
1
1
5
orders_baseline
543
279
147
80
48
1500000000
part_baseline
81
42
24
16
11
200000000
supplier_baseline
9.1
9
9
4
9
10000000
We recommend using an XL warehouse for high-volume data copies, as indicated by
the timings shown in Table 3-1.
ChapTer 3
The Query profiler