Snowflake in-db browse very slow

[fmvizcaino]

Hi community,

I have been facing some issues with a client’s project that uses Snowflake and in-DB.

When I run this workflow, it takes 3.5 seconds to complete, but the same workflow with the in-DB Browse takes 30 minutes. Any idea what could be causing this?

The ODBC configuration has nothing out of the ordinary as well.

Thank you,

Fernando Vizcaino

[Cedric · Accepted solution]

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!

[apathetichell]

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

[Cedric]

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 

[apathetichell]

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).

[Cedric]

Yes. I'm not a novice and I understand that. What I left out was that I was only taking about 250MB of data in limited columns.

The thrust of the issue was the logging, and that is a function of the connection string INDB. That is the parameter that controls the speed of the query when pushing it to memory. And that was the solution to this particular issue.