Code Optimization

[kuppusamy]

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 ?

[LinusH]

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.

[kuppusamy]

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

[Sajid01]

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

[SASKiwi]

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.

[LinusH]

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.

[Kurt_Bremser]

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.

[Ksharp]

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

Try READBUFF= option.