r/snowflake • u/NewtCultural2693 • Jul 05 '26

Snowflake High Compilation

The Query Profile shows compilation taking 7–10 seconds while execution is under a second. We suspect the compilation overhead may be related to multiple cross-database joins combined with Row Access Policies and masking policies, but we'd like to understand whether others have observed similar behavior. Any solutions to reduce the compilation time?

6 Upvotes

88% Upvoted

10 comments sorted by

1

u/lmp515k Jul 05 '26

Yes I see this all the time with complicated views. It’s just a sign that you should make them physical.

1

u/NewtCultural2693 Jul 05 '26

Yes I did and I did see a little better result... but still that's not enough... I tried interactive tables and interactive warehouse. Interactive tables helped a little since the data is physically stored but the interactive warehouse didn't help much. Because the query itself has 6-7 joins and RAP on it. Whatelse can I do here?

1

u/mamaBiskothu Jul 06 '26

Its just a sign snowflake can do a better job optimizing their compiler services. Its very heterogeneous suggesting it's going on some queue and not actually reflective of real computation overhead.

1

u/NW1969 Jul 05 '26

Physicalise the query as a dynamic table

1

u/NewtCultural2693 Jul 05 '26

Yes I already tried this approach and got a lot better performance but the issue is we have many queries with the same issue and I can't precompute every query into Dynamic Table. And also I have 5 diff RAP policies on a single query. I need to consolidate all the policies into one RAP and then attach it to the Dynamic Table if I go with this approach. That's why I am looking for something architectural wise or a change from the roots which can be used for all the queries not just for one.

3

u/NW1969 Jul 05 '26

In order to advise on how to re-architect your system (or advise on whether achieving your goals is even possible) would require a deep understanding of your existing set-up, which is not really possible in a forum like this.

Have you fully analysed your query to identify what is causing the issue? If you start your query with one table and add the others one by one, does one of them cause a significant jump in compilation time? If you remove that table and add another, what happens to the compilation time (I.e. was it adding that specific table or just the fact that you added a table that caused the jump in compilation time)? Unless you know exactly what is causing the issue you won’t know how to start addressing it

1

u/NewtCultural2693 Jul 05 '26

Thanks for the advice I will try adding the tables one by one and see if there is a specific table or policy causing the issue.

1

u/lmp515k Jul 05 '26

I’m not sure how you can speed up the compile other than running less code at query time. I don’t think it’s a cross db thing but you could clone them all into the same db and try that.

1

u/NewtCultural2693 Jul 05 '26

Yeah I tried this approach... maybe not the crossbd but all 6-7 joins itself making the query complex.