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!
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.
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:
PROC SQL NOPRINT;
SELECT VBL INTO: VBL_VALUES SEPARATED BY '", "'
SET ORALIB.ORATBL (ORHINTS='/*+ INDEX(ORATBL, VBL_INDX) */');
WHERE VBL IN ("&VBL_VALUES");