topic Re: Code Optimization in SAS Enterprise Guide

Code Optimization

kuppusamy — Mon, 05 Dec 2022 12:12:29 GMT

I need to extract the data from oracle (Transactional data) but it is taking too much time. For extraction single date data, its taking 5min.

I tried,

Passthrough query

normal proc sql by assigng library

not worked. Any idea ?

Re: Code Optimization

LinusH — Mon, 05 Dec 2022 12:26:23 GMT

Some hard facts could help understand your scenario (table structure, example query, logs).

But given what you wrote, I don't think it's an optimizing code task.

Extracting data performance depends on indexing/partitioning in the database, and potentially the bandwidth from SAS to the database server.

What is your task generally speaking?

5 minutes doesn't sound like a lot of time if it should be a daily batch.

Also, consider put the subsequent processing in the DB to minimize transport of data.

Re: Code Optimization

kuppusamy — Mon, 05 Dec 2022 12:45:22 GMT

Do you have any code or program options which is different from normal?

Re: Code Optimization

Sajid01 — Mon, 05 Dec 2022 13:56:34 GMT

Rather that pull date from database, take processing to where the data resides.

You may get some ideas here https://documentation.sas.com/doc/en/pgmsascdc/v_033/indbug/n1lo1d19ercldkn14vm45k13z3so.htm

Re: Code Optimization

Kurt_Bremser — Mon, 05 Dec 2022 19:58:20 GMT

Please post the complete log of the SQL step that runs too long.

Also let us know how many observations you pull, and the observation size.

Re: Code Optimization

Ksharp — Tue, 06 Dec 2022 11:39:40 GMT

libname x oracle datasrc=....  readbuff=10000 bulkload=yes  ;
proc copy in=x out=work memtype=data ;
select your-table-name ;
run;

Try READBUFF= option.

Re: Code Optimization

SASKiwi — Fri, 09 Dec 2022 02:06:44 GMT

Run a test converting your query into a count of the rows being selected. If this is no faster than reading the selected data into a SAS dataset then it is an Oracle query performance problem. I've found Oracle hints can be helpful for this. A row count test looks like this:

proc sql;
select count(*)
from <My Oracle table>
where <My where clause>
;
quit;

If it is a lot faster than transmitting of the data across your IT network back to SAS (CREATE TABLE) is the bottleneck.

Re: Code Optimization

LinusH — Fri, 09 Dec 2022 10:08:35 GMT

READBUFF (as @Ksharp shows in his example) is the only option I know about that could affect performance in the data transfer from Oracle.

BULKOAD does not help here, it helps you when you want to load Oracle tables.

So again, network bandwidth, and Oracle query performance is the two other areas you can look into.