Help using Base SAS procedures

extremely slow PROC SQL with a CREATE TABLE with join to a local library

Reply
N/A
Posts: 0

extremely slow PROC SQL with a CREATE TABLE with join to a local library

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!
Trusted Advisor
Posts: 2,115

Re: extremely slow PROC SQL with a CREATE TABLE with join to a local library

Posted in reply to deleted_user
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.
Respected Advisor
Posts: 4,173

Re: extremely slow PROC SQL with a CREATE TABLE with join to a local library

Posted in reply to deleted_user
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
Regular Contributor
Posts: 171

Re: extremely slow PROC SQL with a CREATE TABLE with join to a local library

Posted in reply to deleted_user
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]
Ask a Question
Discussion stats
  • 3 replies
  • 495 views
  • 0 likes
  • 4 in conversation