Snowflake Optimize Query Performance - Transactional Type Queries

Question

I am wondering if anyone has optimized a Snowflake table to support transactional type queries and the steps you took.

Example Table:

CHILD_ID	PARENT_ID
1	9
2	9
3	4

Example Query:

SELECT PARENT_ID
FROM TABLE
WHERE CHILD_ID = 1

What would be the best way to run these types of queries? Should I do a clustering key on CHILD_ID? Should I have these queries run on an XS warehouse? In the query profile, it seems like most of the time is spent on Initialization.

Two things specific to clustering key here - you should choose one that does not have all unique values and ideally clustering key columns should form part of predicate. You can refer here - docs.snowflake.com/en/user-guide/… — Pankaj
– Pankaj, Commented Apr 4, 2022 at 19:57
Since CHILD_ID is all unique values - so I shouldn't do clustering per the snowflake documentation. — Grant Culp
– Grant Culp, Commented Apr 4, 2022 at 20:12
Please note that snowflake clustering is not same as indexing in other traditional RDBMSs. Its more like partition key to help distribute your data, so when its queried, pruning can help to access fewer micro-partitions there by making query faster. — Pankaj
– Pankaj, Commented Apr 4, 2022 at 20:15

Clark Perucho · Accepted Answer · 2022-04-04 21:01:58Z

1

Unless you're doing just a rewrite to optimize your query, a more detailed analysis is necessary if you're looking to apply features like Automatic Clustering as it will not only affect a single query but possibly all queries accessing the table. It is not something that one would recommend after seeing only a single use case of a table.

Although in the above case, Search Optimization Service seems to be the more appropriate solution. I've summarized Snowflake's query tuning options below and I think it can give you some ideas: https://medium.com/snowflake/snowflake-accelerate-query-performance-767663b0e5b7

answered Apr 4, 2022 at 21:01

Clark Perucho

4662 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Snowflake Optimize Query Performance - Transactional Type Queries

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related