PROC SQL vs PL/SQL

[Cherry]

I have some PROC SQL code that has to be terminated due to excessive time(>900 min) when run via PC SAS, but when I run the SQL code in PL/SQL it takes less than five minutes to run. Any suggestion on this?

[DF]

I occassionally have this problem querying Oracle too.  Have you tried running the code in pass-through mode?

SAS seems to do pretty awful things with SQL sometimes.  Pass-thru will essentially have Oracle run your code and do all of the work, meaning it'll also take advantage of indexing and other Oracle optimizations.

proc sql;

connect to oracle (user=user pw=pass path='DBNAME');

create table MyTable as

    select *

    from connection to oracle

    (

        SELECT

        1

        FROM

        DUAL

     );

disconnect from oracle;

quit;

[SASJedi]

To get some visibility into what's going on, try the SASTRACE option.  Using this option puts extra info in your log about what portions (if any) of the query are passed to the DB and what was processed in SAS.

Here's a generic program template for you to experiment with:

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

libname MyOra oracle user=UserID password=MyPW

                                 schema=SchemaName;

proc sql;

select *

  from MyOra.some_table_name

   where Date < '26JUN1954'd

;

quit;