topic sorting large dataset in SAS Programming

sorting large dataset

aamoen — Wed, 04 Oct 2023 21:02:57 GMT

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;

Re: sorting large dataset

Kurt_Bremser — Wed, 04 Oct 2023 21:09:25 GMT

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

Re: sorting large dataset

SASKiwi — Wed, 04 Oct 2023 21:11:54 GMT

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.

Re: sorting large dataset

aamoen — Wed, 04 Oct 2023 21:23:03 GMT

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.

Re: sorting large dataset

SASKiwi — Wed, 04 Oct 2023 21:34:51 GMT

Sorry, I forgot the SELECT in front:

select count(*) as row_count

Re: sorting large dataset

AhmedAl_Attar — Wed, 04 Oct 2023 21:35:53 GMT

Hi @aamoen

Have a look at these two papers

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

Re: sorting large dataset

Patrick — Thu, 05 Oct 2023 04:12:50 GMT

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.

Re: sorting large dataset

s_lassen — Thu, 05 Oct 2023 08:29:29 GMT

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.

Re: sorting large dataset

LinusH — Thu, 05 Oct 2023 10:57:41 GMT

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).