page300
388
Referenced Objects
When tuning SQL, you must understand the type of object referenced. Object naming
conventions alone are no guarantee of the underlying object type; you must explicitly
know whether your code addresses tables, external tables, hybrid tables, dynamic tables,
views, or materialized views.
For dynamic tables, views, and materialized views, the distinction is important. All of
these objects contain stored queries that must be executed to return a summary data set
before being joined to other tables and views. You may also encounter the following:
•
Latency for dynamic tables due to underlying data changes not being
immediately reflected into the object
•
Performance penalty for materialized views where Snowflake either
updates the materialized view or uses the up-to-date portions of the
materialized view and retrieves any required newer data from the
base table
•
Views and secure views exhibiting different performance
characteristics
•
Increased costs for maintaining dynamic tables and materialized
views for frequent underlying data changes
You can find more information on views, secure views, and materialized views at
https://www.linkedin.com/pulse/materialized-view-vs-secure-regular-
minzhen-yang/
.
Nesting logic in views is a common way to abstract (or hide) complexity, and
experience suggests performance issues may be buried inside views.
Identifying Object Types
When performance tuning SQL statements, you must identify all in-scope objects for the
current role.
Let’s start by setting the execution context.
SET tpc_owner_role = 'tpc_owner_role';
SET tpc_warehouse_XS = 'tpc_wh_xsmall';
SET tpc_database = 'tpc';
SET tpc_owner_schema = 'tpc.tpc_owner';
ChapTer 10
OpTimizing perfOrmanCe