Improve code efficiency when retrieving data from Snowflake via SAS

[sai212]

Hi,

We are moving away from Greenplum database to Snowflake. Testing a few data retrieving queries, I noticed the run time is longer compared to what it was in Greenplum. I realized this was because the lengths of a few variables were huge (32767). I used to limit these variables length using VARCHAR or CAST statements for Greenplum but am not sure how to do it for Snowflake. There's a thread within the forum discussing the use of readbuff and other options to improve connectivity for Snowflake and I wanted to how could these be used in SQL Passthrough queries. Appreciate your inputs on this.

PROC SQL;

CONNECT TO GREENPLM(DSN = GREENPLM USER = USERID PASSWORD=PASSWORD);

CREATE TABLE CUST_INFO AS SELECT * FROM CONNECTION TO GREENPLM(

SELECT FIRST_NAME :: VARCHAR(50) AS FIRST_NAME,

LAST_NAME :: VARHCAR(50) AS LAST_NAME /* these fields are limited to a length of 50 */

FROM CUST_DATA);

PROC SQL;

CONNECT TO ODBC AS SNWFLK (DSN =SNOWFLAKE USER=USERID PASSWORD=PASSWORD);

CREATE TABLE CUST_INFO AS SELECT * FROM CONNECTION TO SNWFLK(

SELECT FIRST_NAME, LAST_NAME  /* these names come with 32767 lengths */

FROM CUST_DATA);

https://communities.sas.com/t5/SAS-Data-Management/Slow-performance-of-ODBC-connection-to-SNOWFLAKE/...

[yabwon · Accepted solution]

Did you try with changing:

... AS SELECT * FROM CONNECTION TO SNWFLK(...

to:

... AS SELECT First_name length 50, Last_name length 50 FROM CONNECTION TO SNWFLK(...

?

To tell to SAS what is Your expectation about lengths.

Bart

[sai212]

Thank you! This works. How do I use this piece in case, I want to select every variable from the table and also define the lengths?

I tried

SELECT *, CUST_FIRST_NAME LENGTH 20, CUST_LAST_NAME LENGTH 20 FROM CONNECTION TO SNWFLK(---

but it didn't change saying those variables already exist in the table. Am I missing something here?

[Tom]

Try connecting to SnowFlake with a LIBNAME instead of the SQL CONNECT statement.  Then you can try using the DBSASTYPE dataset option.

Another advantage of using the LIBNAME is that you can run PROC CONTENTS on the SnowFlake table to get the list of variables.

If you have the list of variables in a dataset then you can use that to generate the code that lists each variable explicitly and sets the appropriate lengths. Either as an SQL query or using the DBSASTYPE option.

[sai212]

Thank you, Tom! But isn't a SQL Passthrough much more efficient than accessing via a LIBNAME statement?

[SASKiwi]

Using LIBNAME statements is not necessarily less efficient than PASSTHRU, particularly if you are just doing simple, single table queries.

One option that might help you is DBMAX_TEXT: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=acreldb&docsetTarget=p14o...

This will enable you to enforce a maximum length on all character variables being read.

[sai212]

I agree LIBNAME not being bad in case of a single table. I'll look into the DBMAX_TEXT option. Thanks for your input! Appreciate it.

[asadiq]

we are in the similar situation.

DBMAX_TEXT or TEXTSIZE option is not worked for us. I believe it works for BLOB types.

did any of you get it work or any other system options to restrict length of the column?

[sai212]

It kind of worked for me using CAST statements..CAST(VARIABLE AS VARCHAR()) AS

[asadiq]

Yes, everyone have to setup the CAST for their query.

But Looking for SAS system options otherwise we may need to setup the views for the users.