SAS Indexes with Oracle

[DanD]

I'm new to SAS and Oracle. I'm using SAS EG. In EG in the server list there is a folder called "Libraries" that has most of the databases that are also in Oracle. It is my understanding that by using these libraries I am really using the Oracle data.

I use a libary statement like this in my code:
Libname ODS ORACLE USER=user PASSWORD="user" PATH=xxpath SCHEMA=ODS;

I run the same query in EG and in TOAD. The one in EG takes about an hour and the one in TOAD takes less than a minute.

Are the indexes in Oracle used to process data when I write code in EG or do I need to create them? If I create a separate dataset in EG and then use it in a join to the other data (through the library) that is in Oracle do I need to create an index on it?

Thanks

[SASKiwi]

I suggest you try PASSTHRU SQL in SAS as this is the exact equivalent of a query in TOAD. Try this:

proc sql;
connect to oracle
( user = user
password = password
path = xxpath
buffsize = 32767
preserve_comments
);

create table example as
select * from connection to oracle
(

* put your TOAD Oracle query here ;

);
disconnect from oracle;
quit;

A query via a SAS LIBNAME requires SAS to translate your query into PL/SQL and that may not be exactly the same as your TOAD version.

[DanD]

I have used PASSTHRU SQL in other instances but my boss prefers not to use it? I will try it to see if it makes a difference in performance for this particular query. By not using PASSTHRU SQL are the indexes in Oracle still used? Would putting an index on my SAS dataset improve performance?

Thanks

[LinusH]

I also think like your boos that explicit pass-thru should be avoided if possible.
SAS will try to convert your SAS SQL query to Oracle SQL.
If the query doesn't contains joins, the most important is that processing such as filtering (WHERE) and summarization takes place at the source.
So you need to fins out how SAS transforms your query.
By using the global options SASTRACE and SASTRACLOC, you can see what actually is going on.
There are a lot of papers, documentation etc that describes how to write your query to make automatically pass to an external RDBMS.

Good luck!

Linus

[DanD]

I found out that if I put the query in some SAS code and run it on the Unix box it takes a little less than 4 hours. I can run the same query in TOAD in under 30 seconds. I don't know what SAS does but it's obviously slow.:) I'll look into thiese thngs. Thanks.

> I also think like your boos that explicit pass-thru
> should be avoided if possible.
> SAS will try to convert your SAS SQL query to Oracle
> SQL.
> If the query doesn't contains joins, the most
> important is that processing such as filtering
> (WHERE) and summarization takes place at the source.
> So you need to fins out how SAS transforms your
> query.
> By using the global options SASTRACE and SASTRACLOC,
> you can see what actually is going on.
> There are a lot of papers, documentation etc that
> describes how to write your query to make
> automatically pass to an external RDBMS.
>
> Good luck!
>
> Linus

[Reeza]

You mention putting an index on your SAS dataset....are you downloading the data required for the analysis and then processing?

Or using proc sql;
similar query as in TOAD?

[SASKiwi]

I would be interested in knowing why your boss does not recommend using PASSTHRU SQL. The only negative I can think of is the syntax differences. If you are familiar with Oracle SQL that problem disappears.

FYI, we use PASSTHRU SQL almost exclusively for complex queries as you can easily add Oracle specifics like HINTS that can dramatically improve performance (for example query parallelisation). And you can test your query in TOAD and then insert it unchanged into your SAS program and expect very similar performance. These are important issues for large, complex production processes that need to run as efficiently as possible. It also removes the issue of having to investigate how SAS is translating your SQL.

I would agree however that SAS SQL and language is the way to go for simpler processes and smaller data volumes where ultimate processing efficiency is not required.

[DanD]

I don't know her reasons. I'm going to test using the passthru, though, considering that it took almost 4 hours to run the query in SAS without passthru and less than a minute in TOAD.

> I would be interested in knowing why your boss does
> not recommend using PASSTHRU SQL. The only negative I
> can think of is the syntax differences. If you are
> familiar with Oracle SQL that problem disappears.
>
> FYI, we use PASSTHRU SQL almost exclusively for
> complex queries as you can easily add Oracle
> specifics like HINTS that can dramatically improve
> performance (for example query parallelisation). And
> you can test your query in TOAD and then insert it
> unchanged into your SAS program and expect very
> similar performance. These are important issues for
> large, complex production processes that need to run
> as efficiently as possible. It also removes the issue
> of having to investigate how SAS is translating your
> SQL.
>
> I would agree however that SAS SQL and language is
> the way to go for simpler processes and smaller data
> volumes where ultimate processing efficiency is not
> required.

[DanD]

I used proc sql but it was too slow so I was trying to figure out how to get the data into Oracle using TOAD. I finally figured it out.


> You mention putting an index on your SAS
> dataset....are you downloading the data required for
> the analysis and then processing?
>
> Or using proc sql;
> similar query as in TOAD?

[DataShare]

try following options, this will enable the query to be sent to database

libname ods oracle user =user,password="pwd" ,path=path,schema=schema;
options DBIDIRECTEXEC;
%let SYS_SQL_IP_SPEEDO=YES;
proc sql;
select * from ods.table1;
quit;

[DanD]

I'll try this. Thanks.

> try following options, this will enable the query to
> be sent to database
>
> libname ods oracle user =user,password="pwd"
> ,path=path,schema=schema;
> options DBIDIRECTEXEC;
> %let SYS_SQL_IP_SPEEDO=YES;
> proc sql;
> select * from ods.table1;
> quit;