BookmarkSubscribeRSS Feed
CameronLawson
Obsidian | Level 7
Hi All,
I was wondering if anyone has tested the following scenarios and can share what sort of performance gain/loss you get compared to pass through sql

1. Using proc append where base is a sas dataset and data is from an Oracle source (and using a where clause)

2. Datastep with a where clause grabbing only changed records from an Oracle source.

3. Datastep view with a where clause to an oracle source as opposed to physically writing the data.

I currently have an ETL process that uses datastep to update / append a local copy of data on our server and want to see if I can make it more efficient. I am particularly interested in the use of proc append to get my daily extracts.

Thanks in advance.
3 REPLIES 3
DanielSantos
Barite | Level 11
Hmmm, I'm confused.

None of the scenarios you are proposing are using SQL Pass Through.

2 and 3 are almost identical. The view must be resolved in order to retrieve the data. So, yes the creation of the view is done immediately, but resolution will take probably the same time as 2 (depends how you are defining the view).

Append works fast, because it does not need to scan the whole base table, only data is effectively processed row by row. And again it depends how data is retrieved.

I would indeed consider adding a 4th scenario, which would be, a SQL Pass Through extract to a sas dataset, followed by proc append to the base dataset. It depends on the amount of data you're trying to retrieve, on the DBMS engine, on how complex is your WHERE condition.

From my experience, you won't get a clear answer without doing some serious testing of the 4 approachs (although I would put aside rightaway 2 and 3).

Cheers from Portugal.

Daniel Santos @ www.cgd.pt.
CameronLawson
Obsidian | Level 7
Apologies for not being clearer. We are currently using passthrough sql. I am playing with different methods and was wondering if anyone else had feedback on using proc append etc.

In datastep with sas data, proc append usually out performs set=, my main question is if this applies when transporting data between Oracle to sas dataset.
LinusH
Tourmaline | Level 20
If you're where-clause is written in a standard way, I suspect you won't get any performance benefits from using SQL pass-thru. The where clause would most likely be executed within Oracle.

/Linus
Data never sleeps

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
  • 3 replies
  • 874 views
  • 0 likes
  • 3 in conversation