page309
397
of programming options than SQL; UDFs can be implemented using Java, JavaScript,
Python, Scala, and SQL.
Whenever you encounter UDFs embedded in SQL statements, the root cause is
typically to abstract very complex logic to return a readily understood answer compatible
with the calling SQL query body. UDFs are called for every row in the calling SQL body
and therefore often result in performance bottlenecks.
Wherever possible, I prefer to remove inline UDFs and instead resolve complex logic
using standard SQL. This approach does not suit all use cases.
You can find more information on UDFs at
https://docs.snowflake.com/en/
developer-guide/udf/udf-overview
.
Identifying Issues
Previous sections in this chapter have addressed how to design for performance and
remediate SQL statements to improve performance. In this section, I focus on identifying
SQL statements by investigating the
information_schema.query_history
view for
metrics. Note the 14-day limit on data retention. Alternatively, you may prefer to use the
corresponding Account Usage Store view, which both retains information for 1 year and
has up to 45 minutes latency.
Warehouse Queueing
Queueing is identified where
queued_overload_time
represents the amount of time the
query waits before execution commences. The following query is offered as a starting
point for your investigation. Chapter 6 contains a thorough investigation of warehouse
queueing from which these queries are derived; please refer to Chapter 6 for a fuller
explanation.
SELECT query_type,
query_id,
query_text,
role_name,
warehouse_name,
queued_overload_time,
execution_time
ChapTer 10
OpTimizing perfOrmanCe