BookmarkSubscribeRSS Feed
jwong9
Calcite | Level 5

Hi,

I use SAS/Access to Oracle to connect SAS to Oracle.  I need to download Oracle data to SAS table.  May I know which is the best method in term of speed:-

PROC COPY

PROC SQL

PROC DBLOAD

DATA step

I have about 5mil records. 

Thanks.

Janice

3 REPLIES 3
Ksharp
Super User

I will pick PROC DBLOAD.

But the speed of ETL is not about these PROC , it is about options in LIBNAMES , like you can add readbuff=10000 and bulkload=yes to fast it .

And Special System option:   options compress=yes to decrease I/O overhead .

Xia Keshan

LinusH
Tourmaline | Level 20

Downloading data without transformation doesn't add value.

I would chose SQL so that any transformations is pushed down prior to the download.

Data never sleeps
JBailey
Barite | Level 11

Hi Janice,

 

The SAS procedures (COPY, SQL, APPEND, etc.), and DATA step, will use the same mechanism to bring data from Oracle into SAS. SAS generates a query and ships it off to the database. WHERE clauses and many function calls will  pass through to the database without you having to do anything special.  There is an exception to this - SQL pass-through (aka. explicit pass-though). In this case you create database specific queries and SAS ships it, as -is, to the database. 

 

You may want to take a look at the READBUFF= option. It may help with your query performance. Recent versions of SAS do a good job of setting this for you.

 

If your table is partitioned, you may want to look at the DBSLICE= option. To be honest, if your table is large enough to require partitioning, reading it into a SAS table is probably not a great idea.

 

Here is an explicit pass-thru example. I include it because the CREATE TABLE AS can be difficult to stumble onto.

 

PROC SQL;

   CONNECT TO ORACLE(PATH=mypath USER=myuser PASSWORD=mypw);

   CREATE TABLE mylib.mytable FROM CONNECTION TO ORACLE

        (some Oracle specific SQL statement);

QUIT;

 

The DBLOAD procedure is used to load data from SAS into Oracle. DBLOAD isn't used much for new code since you can run bulkload using dataset and LIBNAME statement options. It's more of a legacy thing.

 

You can see the SQL being sent to the database using this systems option.

OPTION SASTRACE=',,,d' SASTRACELOC=saslog NOSTSUFFIX;

 

Ideally you want to ensure that joins, WHERE clauses and functions (mainly when used in a WHERE clause) are passed to the database.

 

Linus brings up a very good point. The real question is, why do you want to download an entire Oracle table into SAS? Typically, it isn't a great idea. It is best to let the database handle much of the work. There are situations where downloading a subset of data is appropriate. For example: I need to repeatedly process a subset of data and it is quicker, in the long run, to pull the data and then repeatedly process it. 

 

You may find this SGF paper useful. It doesn't address your specific question, but it will help you learn about SQL query performance. 

The SQL Tuning Checklist: Making Slow Database Queries a Thing of the Past

 

Good luck!

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 2523 views
  • 1 like
  • 4 in conversation