topic Re: Queyring Snowflake data is super slow in SAS Enterprise Guide

Queyring Snowflake data is super slow

sonnysangBB — Mon, 05 Feb 2024 17:54:27 GMT

Hi everyone,

I am using SAS EG 8.3 to query Snowflake database. I wrote a query like below:

proc sql;
select count(*) as cnt from (
select
   col1, col2, col3... (there are 12 columns)
from tbl1
inner join tbl2
on ...
inner join tbl3
on ...
where ...
)
quit;

the expected cnt should be 337,947 because I ran similar code in snowflake successfully (less than 1 minute). But I got errors saying

ERROR: Insufficient space in file WORK.'SASTMP-000000054'.UTILITY.
ERROR: File WORK.'SASTMP-000000054'.UTILITY is damaged. I/O processing did not complete.
ERROR: There is not enough space to store some intermediate results during join processing.

My question is how to modify the code so it can run in SAS? Because I need the 337,947 records for further analysis.

To me, I feel the code is trying to pull all data into local machine before aggregation. Is the code supposed to be run in Snowflake?

Thanks!

Re: Queyring Snowflake data is super slow

Tom — Mon, 05 Feb 2024 17:58:36 GMT

Move it into the Snowflake server.

Say you have a libref named SNOW pointing to Snowflake then use code like:

proc sql;
connect using snow;
select cnt from connection to snow (
select count(*) as cnt from (
select
   col1, col2, col3... (there are 12 columns)
from tbl1
inner join tbl2
on ...
inner join tbl3
on ...
where ...
))
;
quit;

Re: Queyring Snowflake data is super slow

sonnysangBB — Mon, 05 Feb 2024 18:13:09 GMT

it works! Thank you @Tom