BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASAna
Quartz | Level 8

Hi SAS Users,

 

Wanted some help in the tuning the below query, which is taking 2 hours to update the Oracle table after Oracle -SAS merge.

 

SAS datset has 1800 records. Oracle table is pretty big, but indexed on merging variables. after merging it is updating 4000 records.

 

 

libname test oracle user=&myid password=&mypwd path="&mydb" schema=test;

 

proc sql noprint; 
update test.&Oracle_table  as a
set
test_id =
(select test_id from sas_dataset as b
where a.&rec_id = b.&rec_id and a.run_id = b.run_id   and a.run_date = b. run_date)
where exists
(select * from sas_dataset as b
where a.&rec_id = b.&rec_id and a.run_id = b.run_id   and a.run_date = b. run_date)
;
quit;

 

libname test clear;

 

 

 

Thanks,

Ana

1 ACCEPTED SOLUTION

Accepted Solutions
kiranv_
Rhodochrosite | Level 12

You can oracle table through implicit pass through.

 

proc sql;

create table oratable.tablename as

select *

from sastable.tablename;

quit;

 

---Then create index

 

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute ( create index on tablename (columns) ) by oracle;
disconnect from oracle;
quit;

 

--then do the update with logic you have 

 

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute ( your update logic goes here) by oracle;
disconnect from oracle;
quit;

View solution in original post

9 REPLIES 9
kiranv_
Rhodochrosite | Level 12

As SAS dataset is pretty small. Move the SAS dataset to Oracle and build index on the table and collect stats. Then do the update through explicit pass through.

 

 

 

SASAna
Quartz | Level 8

Hi SAS users,

 

I tried this below as per your suggestion of adding option and creating the test table in ORacle DB.  But i am getting error.

 

Can u please correct my mistake?  I needed to create a empty table - so there is no "from".

 

Error - right paranthesis is missing.

 

code - 

 

 

options dbidirectexec sastraceloc=saslog;

 

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute(
create table test.post_test as
select
(job_id char(10),
sale_id char(20),
reason  char(2000),
run_id char(20),
run_date char(20)

)
);
disconnect from oracle;
quit;

kiranv_
Rhodochrosite | Level 12

You can oracle table through implicit pass through.

 

proc sql;

create table oratable.tablename as

select *

from sastable.tablename;

quit;

 

---Then create index

 

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute ( create index on tablename (columns) ) by oracle;
disconnect from oracle;
quit;

 

--then do the update with logic you have 

 

proc sql;
connect to oracle (user=&myid orapw=&mypwd path="&mydb");
execute ( your update logic goes here) by oracle;
disconnect from oracle;
quit;

SASAna
Quartz | Level 8
Thanks Kiran for the detailed steps. It worked very well and improved the processing time.
Sven111
Pyrite | Level 9

Given the small size of the SAS dataset, I'd recommend pushing it across to Oracle into a temporary table and doing the full update there locally using explicit SQL passthrough.  I've had mixed results using implicit passthrough SQL with Oracle in the past, particularly when I'm trying to join/reference SAS datasets with Oracle tables like you're doing.  I always try to push everything into either SAS or Oracle if I can help it.  Plus, using explicit SQL passthrough will let you add optimizer hints if needed to make sure it's using the indexes properly (although there are some tricks to that too I can share if you're interested).

SASAna
Quartz | Level 8

Yes please. I am interested to know that 🙂

 

Thanks,

Ana

Sven111
Pyrite | Level 9

There were a couple of things that tripped me up on Oracle hinting from Explicit Pass-Through SQL.  The first is that by default, SAS strips out any comments from the pass through, and since Oracle hints are passed as comments it won't send them by default.  Depending on how you generally connect to Oracle from PROC SQL and what version of SAS you're on this can either be an easy fix or not.  If your doing an explicit connection like this:

connect to oracle (user=myusr1 password=mypwd1);

then you can just add the paramater PRESERVE_COMMENTS:

connect to oracle as mycon(user=myusr1 password=mypwd1 preserve_comments);

But if you're using something older than SAS 9.2, even that won't work.

 

If you generally connect to Oracle via a LIBNAME statement (which is convenient since you can then use the same connection for both implicit and explicit pass-through SQL, there is no available option to enable you to preserve comments.

LIBNAME OracSAS     ORACLE     USER=&UserNm    PASS=&PassWd   PATH='SASDB'  CONNECTION=GLOBAL   SCHEMA=&UserNm;

PROC SQL;
        CONNECT USING OracSAS AS OracDB;
        ...SQL STUFF...
        DISCONNECT FROM OracDB;
;QUIT;

You can get around this problem (and the problem with old versions of SAS) by wrapping parts of the comments you want to use in %BQUOTE.  I've added the following to my autoexec.sas file and then can just reference the macro vars as needed in my explicit pass-through code.

    %LET ORAC_HINT__APPEND      = %BQUOTE(/)%BQUOTE(*)+ APPEND %BQUOTE(*)%BQUOTE(/);
    %LET ORAC_HINT__PARALLEL_16 = %BQUOTE(/)%BQUOTE(*)+ PARALLEL (16) %BQUOTE(*)%BQUOTE(/);

I've also archived some papers I've found over the years that have been useful:

http://www.lexjansen.com/nesug/nesug05/io/io8.pdf

http://support.sas.com/resources/papers/proceedings13/072-2013.pdf

http://support.sas.com/resources/papers/proceedings13/081-2013.pdf

http://www2.sas.com/proceedings/sugi28/151-28.pdf

 

Hope that helps!

SASAna
Quartz | Level 8
Thanks for the links and tips , it is really helpful 🙂
LinusH
Tourmaline | Level 20
There have quite a few similar questions on the communities if you care to search.
One hint though: DBKEY=.
Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 3204 views
  • 0 likes
  • 4 in conversation