Taking long time to get table from SQL Synapse table , consuming much space

[Mushy]

Hi All,

With the execution of below query

libname synapse sqlsvr user=xxxx pw=xxxx schema="xxxxxxx";'
data test_1 (compress=yes);
set synapse.test_1
run;

its,  taking log time to get the table from SQl synapse table , also consuming too much space

Anyone who has experience similar issue and fixed it ?

Thanks,

Mushy

[LinusH]

Regardless of use case, for analyzing performance you need to provide more information about your task. Like:

- Where does your SAS session executes (vs your Synapse instance).

- If SAS is on premises, what is your method of accessing Synapse (internet, direct route...)

- Have you possibility to tr another client tool to perform a similar operation?

- What is column definitions does your table have? I suspect you have some long varchar columns.

- Rerun the query and add the following options:

options msglevel=i sastrace=',,,d' nostsuffix sastraceloc=saslog fullstimer;

Please review optimization options for your libname statement, like READBUFF.

[Amir]

Hi,

A few questions so that more information is shared:

1. How much space are you expecting the output table to occupy and is this based on the input table?
2. How long is the process taking and how long were you expecting it to take?
3. Have you run this before without time and space issues?
4. Please share the log by using the Insert Code icon "</>".

I have in the past worked at a site where compress = yes was the default system option, and based upon the log, the size would not necessarily always be reduced. So, perhaps try without the compress = yes option. You can test out various settings with a smaller amount of observations by using the obs= data set option, to reduce your testing time.

Thanks & kind regards,

Amir.

[SASKiwi]

You will get more useful answers if you post the actual SAS log of your program including notes rather than posting code that contains a syntax error.

[Ksharp · Accepted solution]

1)You could try some options of libname and proc sql.

options dbidirectexec bufno=100 bufsize=128k ;
LIBNAME SQL ODBC DATAsrc=proddb SCHEMA=sas U
SER=user PASSWORD="*******" INSERT_SQL=YES 
INSERTBUFF=32767
readbuff=10000
bulkload=yes
dbcommit=10000
;
proc sql;
create table test_1 as
select * from sql.test_1;
quit;

2) try Pass-through SQL.

proc sql;
connect to odbc as MyODBCName (dsn="MyODBCName" user="username" password="password"  readbuff=10000  );

create table test_1 as
select * from connection to MyODBCName (
select * from sqlsrv.test_1)
)
;
disconnect from MyODBCName ;
quit;

[LinusH]

INSERTBUFF is the option to consider here. BULKLOAD and DBCOMMIT won't affect read operations.
Pass-through will probably have the same performance as library access. But, you could use pass-through to shorten wide columns before transfer to SAS (which would also probably work using implicit pass-through using PROC SQL and LIBNAME).