sorting large dataset

[aamoen]

Hello - I'm trying to run the below code but its taking hours.  What can I do to improve the efficiency of this program so it doesn't take so long to run?

Thanks!

%let user = xxxxx;

%let pswd = "%STR({SAS002}EE9F3A145162A68C09E176074A3382401380A5B759B196C62EF8B5D5)";

libname here '/workspace/cucr/risk/userid/xxxxx';

proc sql;

options nomprint;

connect to oracle as adm (USER=&user. PASSWORD=&pswd. PATH="prod");

options mprint;

create table here.v_chd_mini_month_end (compress=yes) as select * from connection to adm

(select chd_open_date, tape_date, chd_credit_line, chd_account_number_ref, chd_cr_line_date

from adm.v_chd_mini_month_end

order by chd_account_number_ref, tape_date);

disconnect from adm;

quit;

[Kurt_Bremser]

I think your main issue is not the sort within the database, but the connection between the SAS and Oracle servers.

Hints for tuning SAS/ACCESS to Oracle:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0fmgbpjwgbnvkn1sysefnr22fow.htm 

[SASKiwi]

To see if the problem is a slow network between the Oracle and SAS servers try just getting a row count:

proc sql;
options nomprint;
connect to oracle as adm (USER=&user. PASSWORD=&pswd. PATH="prod");
options mprint;
 
create table here.v_chd_mini_month_end (compress=yes) as select * from connection to adm
(count(*) as row_count
from adm.v_chd_mini_month_end
);
 
disconnect from adm;
quit;

If this is slow, then talk to your Oracle DBA as it is Oracle that is slow, not SAS.

[aamoen]

When I tried that I recieved the following err:

ERROR: ORACLE prepare error: ORA-24333: zero iteration count. SQL statement: count(*) as row_count from adm.v_chd_mini_month_end.

[SASKiwi]

Sorry, I forgot the SELECT in front:

select count(*) as row_count

[AhmedAl_Attar]

Hi @aamoen 

Have a look at these two papers

• SAS-Oracle Options and Efficiency: What You Don’t Know Can Hurt You
• Tips for Pulling Data from Oracle® Using PROC SQL® Pass-Through

Here is what you need to pay attention to

- ReadBuff/BuffSize: How many Oracle Rows/Records are read for each fetch operation. Default:250 (Too-Little! --- Increase this if you can)

- SASTrace option: Tells you what's going on and where the query is running

- Oracle Hints: They could optimize Oracle execution, and speeds up your response times

Hope this helps,

Ahmed

[Patrick]

I concur with others that the bottleneck is normally the data transfer from the database to SAS. In my experience increasing the value for readbuff is what often decreases elapsed time dramatically. The default value is almost always way too low.

[s_lassen]

Your headline reads "Sorting large dataset" - have you tried not having Oracle sort the data (drop the ORDER BY clause), and sort in SAS afterwards. If it is the actual Oracle server (and not the connection) which is slow, that may help on the performance. But I would probably try looking at the connection options first.

[LinusH]

My question is how you intend to use the result table?

The general idea is to keep large tables in the source until you need them for your final step. In this case you might continue with filtering, joining and what not.

Be aware of that SQL implict passthrough automatically sorts your data if the database libname engine encounters a BY statement (PROC, data step etc).