BookmarkSubscribeRSS Feed
vomer
Obsidian | Level 7

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?

10 REPLIES 10
robby_beum
Quartz | Level 8

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;

vomer
Obsidian | Level 7

Thanks for the suggestion! I do need the entire table so unfortunately I cannot limit to a subset of IDs.

Tom
Super User Tom
Super User

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.

TomKari
Onyx | Level 15

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

vomer
Obsidian | Level 7

@ Tom

Is this something I set in sas? Or on database end?

If in SAS how and where should this go?

Thanks!

TomKari
Onyx | Level 15

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

SASKiwi
PROC Star

Try the READBUFF= option on your ODBC connect statement: READBUFF = 32767. 32767 is usually the maximum setting depending on the database you are reading.

vomer
Obsidian | Level 7

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;

??

vomer
Obsidian | Level 7

Thanks Tom, can someone please tell me how to use the readbuff in my example query setup?

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1223 views
  • 0 likes
  • 5 in conversation