sfc-gh-jmichalak
commented
2 weeks ago Hi @rd-thomas-uhren :wave:
This is related to #3118. As announced on our roadmap, we plan to target the performance of running similar/subsequent queries. Caching is one of the potential solutions to improve the performance. But the options currently on the table are: caching, batching requests, and/or not reading the state initially at all. They all have their pros, cons, and challenges.
Unfortunately, we can't improve the performance of a single SHOW WAREHOUSES query for now. However, if you split your deployment into smaller ones, the number of such queries will be reduced, leading to a potential time improvement of the plan.
Use Cases or Problem Statement
We observed and confirmed by engineering that the performance of "SHOW WAREHOUSES" and "SHOW WAREHOUSES LIKE 'WAREHOUSE_NAME'" is identical. Since we have a lot of warehouses (~1000) the command ("SHOW WAREHOUSES LIKE 'WAREHOUSE_NAME'") takes approximately 10 seconds to complete; again for each of the 1000 warehouses.
As a result, the build step takes a very long time and is responsible for more than 80% of the total running time.
Engineering proposed to run the SHOW WAREHOUSES once and query the result cache for subsequent calls.
This would greatly improve the running times.
Category
category:resource
Object type(s)
No response
Proposal
Engineering proposed to run the SHOW WAREHOUSES once and query the result cache for subsequent calls.
How much impact is this issue causing?
Medium
Additional Information
No response
Would you like to implement a fix?