Snowflake in-db browse very slow

Hey @Cedric ,

In my case, the issue was related to the query generated by Alteryx. Our table had more than 2 hundred column and even using select *, Alteryx were translating that into a ginormous query instead of keeping the *.

You can use the dynamic output in-db to check your query.

In addition to that, for in-db workflows with a lot of tools, I suggest including temporary tables in a few places there with the Write Data In-DB tool to simplify the resulting query.

Best,

Fernando Vizcaino

I'm going investigate this. Because you're right, I'm at like 150 columns and I did a SELECT * and then a summarise (SELECT DIstinct) before adding the WHERE.
I think I'm going to select the fields that I was looking for in the summarise and add the where either in the original query or just after the input--I bet it will help paring down the fields.

The fact that I have to manage this makes me a little itchy, but I'm really glad people like you respond and help me out. Thanks @fmvizcaino ! I'll get back to you with some metrics (I'll probably just edit this post).

--EDIT:
In the Snowflake terminal, I discovered my group-by had no impact. So I just selected the fields I needed, added a WHERE Filter, and the query looks good in the Dynamic INDB-out.
I found the output was still painfully slow at like 1MB per minute. I had a feeling it is everything to do with the INDB nature of this.

I looked up my connection string and found the following:

odbc:DRIVER={SnowflakeDSIIDriver};SERVER=...snowflakecomputing.com;PORT=443;DATABASE=[db];WAREHOUSE=[wh];ROLE=ALL;TRACING=6;authenticator=https://...okta.com;UID=...;PWD=__EncPwd1__;

That TRACING issue is the problem. I changed that to a 0 and the query rendered in under 10 seconds! Huzzah!

All comments

Are you using DSII or the "recommended" driver? Is this on SSO?

my test run was 100 record browse in 4.5 seconds In-DB - (source has about 20 fields and "a lot" of records)

https://help.alteryx.com/20221/designer/browse-db-tool

Hi @apathetichell ,

Thank you for your answer.

We did our test with the drivers available on the data source's page (not sure what DSII means 😅). https://help.alteryx.com/20221/designer/snowflake

No SSO.

Best,

Fernando Vizcaino

ArtApa

Hi @fmvizcaino - Do I understand correctly that the workflow runs slower if you use a Browse In-DB tool?

If that's the case then check the configuration of your Browse In-DB. In-DB tools push down processing to the data source side. However, Browse In-DB will download data from your data source to the Desktop to allow you to see a preview in the results window. This looks like a bottle neck, which causes a delay.

@fmvizcaino On that page there are two distinct drivers - the "approved" SIMBA driver and the "Not Certified Driver From External Provider" - the later is the Snowflake provided DSII driver. I use it. I set up via SAML/SSO - but my hunch is service account would be just as good.

my related question would be if you are setting a default compute warehouse and if it's a size x-small... I'd expect this would affect input data as well though.

Thank you both for the answers.

@ArtApa , I understand that the browse in-db tool downloads the data, but from 3.5 seconds to 30 minutes for the basic 100 rows download seems too much.

@apathetichell , I didn't know that, thanks! We are using a service account to run and have tested both drivers.

if you want to have a call on this - I can try to walk you through some config options - this behavior is pretty odd. My config is incredibly simple for the DSII and my connection string in-DB is pretty straight forward too.

If I didn't see anything glaringly wrong I'd recommend creating a new configuration in ODBC 64 for a system vs a user (or a user vs a system) and then creating a new In-DB connection. I'd also recommend leaving default compute warehouse blank - if possible, and retesting this on a really simple query (refresh tables and choose a random table)... If there is a syntax issue in the query Snowflake might get unhappy....

ddiesel

Hi @fmvizcaino!

I'm subscribing to this post because I'd love to hear of your outcome. I haven't experienced this issue specifically with Snowflake, but there have been other times I have noticed significant latency in the In-Database tools vs. regular input. Please let us know what you find out.

Thanks,

Deb

I'm having the same issue, roughly. I have a query that is about 900k rows (250MB, more or less) and it took 4s in the Snowflake UI and took me 4 hours in my INDB data-stream-out tool.

That is... ridiculous.

I'm using the Snowflake DSII Driver rather than the Simba driver; it shows up as Snowlake ODBC in my configuration on the read side (and on the write side, I am getting the bulk loader, which I think would in theory be a good thing). The instance I have in Snowflake is an x-small, but, again, 4 hours for 250MB is just... not right.

Hey @Cedric ,

You can use the dynamic output in-db to check your query.

In addition to that, for in-db workflows with a lot of tools, I suggest including temporary tables in a few places there with the Write Data In-DB tool to simplify the resulting query.

Best,

Fernando Vizcaino

@Cedric- quick clarification here - are you talking about browse in-db speed or speed with something downstream (ie after a datastream out)...

It was at the juncture, i.e. the INDB-out-to-stream tool; that's where the query is rendered. I didn't really realise that there was a tracing variable present and that it has such a large impact on the query. As far as I understand it, though, I'm not sure why I'd need any query logging in an Alteryx workflow, so I shut off the tracing variable by setting it to 0 in the connection string.

And just so it is here:

Specifies the level of detail logged for clients that use the ODBC driver:
0 = Off
1 = Fatal
2 = Error
3 = Warning
4 = Info
5 = Debug
6 = Trace
-https://docs.snowflake.com/en/user-guide/odbc-parameters.html

Datastream out takes your data from DB into ram (ie onto your system) for larger amounts of data - this can take a huge amount of time. I believe this was thread previously talked about a browse in-db being slow a browse in-db would be a 100 record capture of data fro your server - that should be fast. It makes sense that Datastream Out is slow (on larger amounts of data).