Snowflake connector 2.0 : Slow performence analyses

[gmvinfopro]

Hi

Yesterday I forced the direct query model to use the latest version of the connectors

Following the tests with the new connectors, I did not notice any improvement and even a degradation

I dug a little deeper

A page like this, which includes 3 graphs

It generates about 165 requests in snowflake

We have many requests that don't take much time

But the amount and the latency time mean that the feeling is not good.

Another example

A page like this is relatively complex and rich

It generates about  1187 requests in snowflake

The analysis of the requetesttests shows many parasitic queries

• SHOW PARAMETERS LIKE
• SHOW COLUMNS IN TABLE
• SHOW PRIMARY KEYS IN TABLE

It looks like it's related to the new Snowflake Connector... We didn't have that before

There are few queries that return rows

• we have: 50/1187

If we look at the queries:

• We have complex requests
• Select de … select de…  select group by de  … select  group by  …
• Easily brings back more than 1M lines

Reverted to the V1.0 implementation of the Snowflake connector.

The consequence is a decrease in the number of Snowflake requests

There are 9 queries

For the case

We find the 51 requests

We no longer have parasitic requests...

We always have complex queries for example this one

2.4s to do analysis/complilation

Snowflake Connector V1/V2 Comparison

In Yellow, requests with a Snowflake V1 connector

In Green, requests with a Snowflake V2 connector

We can see that the queries in V2 seem much more efficient ...

But the feeling, remains lower than the V1 ... I think it's because in V2, there are too many metadata calls compared to data queries.

I think that's a point to solve

Comparison from a query point of view.. V1/V2

It's the same request except

• In V1, the connector uses wildcards to pass the parameters (? in the request)
• In V2, the connector passes the values directly into the query.

In conclusion

The V2 connector is much more efficient when executing Snowflake queries, you should have a factor of almost 10..

BUT... The problem is that there are too many metadata calls compared to data queries and this hurts the final feel in Power BI

The logic of the query has not changed when moving to V2 (except that there are no more parameters). The complexity remains the same.

In the next update version of the Snowflake connector, the problem of the number of metadata requests will have to be fixed.

Currently, the Snowflake V 2.0 connector is not operational.

Do you think that updating the V2.0 connector will resolve these issues? 

[joshuawoods-tk]

Has the general issue around this been addressed? I am noticing that the 2.0 Implementation takes nearly 2x as long to query a data set of ~3.3 million rows with around 45 columns. 

[v-dineshya · Accepted solution]

Hi @gmvinfopro ,

Thank you for reaching out to the Microsoft Community Forum.

Hi @lbendlin , thank you for your prompt response.

Hi @gmvinfopro ,

For V2 Connector Excessive metadata calls like SHOW PARAMETERS, SHOW COLUMNS, SHOW PRIMARY KEYS for every visualization element. This leads to hundreds or thousands of extra requests, creating latency and poor user experience in Power BI.

Please try below things to fix the issue.

1. Some versions allow disabling metadata checks or enabling caching. Look for options like "EnableMetadataCaching " or similar in the connector or Power BI settings.

2. Please check if metadata calls happen per visual or per query refresh.

Please refer below link.

Power Query Snowflake connector - Power Query | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @gmvinfopro ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @gmvinfopro ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[lbendlin]

Great investigation

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .