topic Re: SAS & Database tables in SAS Procedures

SAS & Database tables

vomer — Mon, 09 Jul 2012 13:59:02 GMT

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?

Re: SAS & Database tables

robby_beum — Mon, 09 Jul 2012 14:20:36 GMT

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;

Re: SAS & Database tables

vomer — Mon, 09 Jul 2012 14:31:48 GMT

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

Re: SAS & Database tables

Tom — Mon, 09 Jul 2012 17:39:25 GMT

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.

Re: SAS & Database tables

TomKari — Mon, 09 Jul 2012 18:46:21 GMT

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

Re: SAS & Database tables

SASKiwi — Mon, 09 Jul 2012 20:30:26 GMT

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

Re: SAS & Database tables

vomer — Tue, 10 Jul 2012 17:01:22 GMT

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;

Re: SAS & Database tables

vomer — Tue, 10 Jul 2012 17:03:09 GMT

@ Tom

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

If in SAS how and where should this go?

Thanks!

Re: SAS & Database tables

TomKari — Wed, 11 Jul 2012 13:51:45 GMT

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

Re: SAS & Database tables

vomer — Wed, 11 Jul 2012 16:56:11 GMT

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

Re: SAS & Database tables

SASKiwi — Wed, 11 Jul 2012 20:15:25 GMT

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.