Read huge Oracle data to SAS-CodePudding

I need to read in a very large Oracle table (half billion) and save it as a SAS dataset. Eventually I just pulled 1/6 of the oracle tables each time and extracted the data 6 times. Simplified Proc pass-through sql query provided below. But it still takes a long time. Any suggestion to further optimize the process so it will be faster/more efficient?

proc sql noprint;
connect to oracle  (user='oooo' password='xxxx' path="ssss" readbuff=10000 preserve_comments);
create table work.sastbl&i. as 
select * from connection to oracle
( select column1,
         column2,
         column3,
         .................
   from oraSchema.oraTbl
%if &i. eq 6 %then %do;
  where &&strt&i. <= memberID
%end;%else %do;
  where &&strt&i. <= memberID
    and memberID < &&end&i.
%end;

);   
%PUT &SQLXMSG ; 
disconnect from oracle;    
quit;
run;

CodePudding user response：

For the most part the main things you need to do are talk to your Oracle DBA and see if you can tune the settings - like READBUFF - to see if you can get a better pipe; or else consider another option than directly reading in SAS (can you schedule an export from Oracle?).

You might want to see if you can compare the time it takes to do the download with the theoretical time - meaning, what is the size of the network pipe to SAS from Oracle, and what is the time to do the query directly on Oracle. If you try to run the code directly in Oracle (SQL Developer, Toad, etc.) and it takes two hours, and SAS takes 2 hours to do the job, then you need to talk to the DBA to see what can be done to improve things; if you run it in 5 minutes in Oracle but SAS takes 2 hours, then you have things on the SAS side to figure out.

CodePudding user response：

Thank you all. Someone also recommended options listed at https://www.mssqltips.com/sqlservertip/5149/bulk-copy-data-from-oracle-to-sql-server/

Just thought it will probably benefit someone someday so I also post it here. I will test it out and see which one is the best option.