[Feature]: Caching of SHOW WAREHOUSES to significantly increase performance

[rd-thomas-uhren]

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?

• Yeah, I'll take it 😎

[sfc-gh-jmichalak]

Hi @rd-thomas-uhren 👋

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.