Hi Guys,
I use SAS to pull some tables from an ODBC connection. Just wondering if there is any way to make this process faster from a code side? I already use the compress function and only pull variables I need. Sample code:
proc sql;
connect to odbc (dsn=&dsn uid=&uid pwd=&pwd);
create table data.table1 as
select * from connection to odbc
(
select SHARED.ID,
SHARED.NAME
from SHARED.REASON
);
quit;
Are there any other ways to make the pull faster?
Don't know if this will help...
If you already have a subset of ID's that you want from this table, you could just pull those ID's instead of all ID's, for example:
proc sql;
connect to odbc (dsn=&dsn uid=&uid pwd=&pwd);
create table data.table1 as
select * from connection to odbc
(select SHARED.ID,
SHARED.NAME
from SHARED.REASON)
as sql1(ID, NAME)
inner join data.Subset_Of_IDs sas1
on sql1.ID = sas1.ID;
disconnect from odbc;
quit;
Thanks for the suggestion! I do need the entire table so unfortunately I cannot limit to a subset of IDs.
Can you switch to using a direct access to the source database rather than using ODBC?
You should get better performance accessing the data from say an Oracle table using SAS/Access to Oracle.
If your connection is set up to pre-read very few records, bumping that number might help. I got a huge increase once going from pre-reading 10 records to 500. Frequently the pre-read is set low because DBMSs tend to be used for transaction processing, not bulk retrievals.
Tom
@ Tom
Is this something I set in sas? Or on database end?
If in SAS how and where should this go?
Thanks!
In the situation I had, it was in the ODBC connection. You'll need to check with your DBMS documentation to find the right parameter.
Tom
Try the READBUFF= option on your ODBC connect statement: READBUFF = 32767. 32767 is usually the maximum setting depending on the database you are reading.
Where would this go in the code? Would it be:
proc sql;
connect to odbc (dsn=&dsn uid=&uid pwd=&pwd) READBUFF = 32767 ;
create table data.table1 as
select * from connection to odbc
(
select SHARED.ID,
SHARED.NAME
from SHARED.REASON
);
quit;
??
Thanks Tom, can someone please tell me how to use the readbuff in my example query setup?
Try: connect to odbc (dsn=&dsn uid=&uid pwd=&pwd READBUFF = 32767) ;
Suggest you also check out the relevant SAS help, either in your installation or online, as all this type of information is there at your fingertips! In my experience changing READBUFF can significantly improve read performance.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.