page295
383
•
Materialize intermediate data sets for use in the session.
•
Statistics inform the optimizer decision-making process.
•
Work with
EXPLAIN PLAN
.
Set LIMIT
While developing applications, you may need to examine a small data sample to identify
filters and test your code functions correctly. Restricting sample data is readily achieved
by adding a
LIMIT
clause to your SQL statements.
Skewed Data
Performance tuning is not a once-off activity. Data profiles change over time, and
INSERT
,
UPDATE
, and
DELETE
operations can cause skewed data where the distribution of
data in a table or database becomes increasingly imbalanced or uneven. The impact of
data skew over time can be significant, particularly when it comes to query performance.
Hash partition joins are used to join large tables, and skewed data may result in
warehouse processing unit overload. Skewed data may be handled more efficiently by
parallelizing data operations, as explained in Chapter 8.
Skewed data may impact clustering. I will discuss clustering keys next.
Ineffective Pruning
Micro-partition pruning is dependent upon several factors including the following:
•
Use of appropriate filter attribute(s)
•
Simple filter expressions, i.e., no operators applied.
•
For unclustered tables, filter attributes matching natural clustering
order of ingested data
Where a table has an explicitly declared clustering key and our SQL statement
predicates do not match the clustering key declaration, a materialized view may provide
an optimal search path noting the additional storage and serverless compute required
for maintenance.
ChapTer 10
OpTimizing perfOrmanCe