topic Re: extremely slow PROC SQL with a CREATE TABLE with join to a local library in SAS Procedures

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

deleted_user — Tue, 05 Oct 2010 18:33:52 GMT

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!

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

Doc_Duke — Tue, 05 Oct 2010 18:43:01 GMT

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.

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

Patrick — Wed, 06 Oct 2010 06:38:34 GMT

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-query

HTH
Patrick

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

polingjw — Wed, 06 Oct 2010 10:39:32 GMT

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]