BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I'm running SAS 9.1 for Windows in an XP environment in a large enterprise with Oracle. I'm a new SAS user and have learned most of what I know from this forum and have a major performance problem I haven't been able to solve. When running a proc sql script to with a create table with a table join in a local library, the performance is EXTREMELY slow (over 10 minutes to return 8 rows with 5 columns). Whenever I query for a more general dataset without a local join the query completes in just seconds however returns far more data than needed and at times difficult to determine why cases were omitted from the results set. Are there options to improve performance in an Win XP environment when joined to a local table to restrict results to the dataset I import? Thanks in advance for your help!
3 REPLIES 3
Doc_Duke
Rhodochrosite | Level 12
When you are joining to a local table, SAS has to bring the entire Oracle table to the SAS server to perform the join. Ouch! Here are three solutions I have seen used.

1) write a table to the Oracle database with your local data. Most DBA's are loath to let "us" do that, but some will do it for a decision support server.

2) do a select against the Oracle table to reduce the amount of data, bringing that to the SAS Server for the join.

3) write code so that your 'small table' is passed to Oracle as fixed text in the query. This works well when the join is based on a key field like "ID" and you can put it into a macro variable for an "IN" operator in the join.
Patrick
Opal | Level 21
Hi

Below some example code illustrating Doc's first option.

proc sql;
connect to oracle;

/* load SAS table into temporary ORACLE table */
execute(
select *
into #mytemp
from mytable
) by sqlsvr;

/* join tables and return result set to SAS */
create table myresult as
select *
from connection to sqlsvr
(
select *
from #mytemp as a
join someothertable as b
on a.key = b.key
);

quit;


Found under: http://www.mathkb.com/Uwe/Forum.aspx/sas/44759/Insert-in-to-temporary-table-for-SQL-pass-through-que...

HTH
Patrick
polingjw
Quartz | Level 8
If Doc’s first option is not possible, in my experience, Doc’s third option usually works well. If you do go with option 3, I highly recommend that you also utilize an Oracle hint to ensure that Oracle uses the correct index. When I have left off the Oracle hint, I have had cases where a particular step executes in less than 10 seconds one day and more than 10 minutes the next. Here is a useful paper that contains information on identifying and using Oracle indexes, as well as many other tips on using SAS to extract data from Oracle databases: www.nesug.org/proceedings/nesug05/io/io8.pdf

Here is some sample code that illustrates option three. The code assumes that ORALIB.ORATBL is an oracle table, SASLIB.SASTBL is a local table, the two tables are being merged together based on the variable VBL, and the ORATBL table contains an index VBL_INDX that uses VBL as a key variable:

[pre]
PROC SQL NOPRINT;
SELECT VBL INTO: VBL_VALUES SEPARATED BY '", "'
FROM SASLIB.SASTBL;
QUIT;

DATA SASLIB.MERGED_TBL;
SET ORALIB.ORATBL (ORHINTS='/*+ INDEX(ORATBL, VBL_INDX) */');
WHERE VBL IN ("&VBL_VALUES");
RUN;
[/pre]

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
  • 2305 views
  • 0 likes
  • 4 in conversation