topic Re: Performance issues in sorting Large Data Set in SAS Procedures

Performance issues in sorting Large Data Set

deleted_user — Thu, 12 Aug 2010 14:14:16 GMT

Hi,

I am having performance issues in sorting huge SAS datasets in z/OS.
Options Used:
options compress=yes reuse=yes STATS MEMRPT FULLSTIMER MSGLEVEL=I bufsize=0
bufno=10 SORTSIZE=MAX UBUFNO = 20 THREADS dbsliceparm=(threaded_apps,4);

No of Records: 48 Million.
LRECL= 800
No of columns in proc sort: 9
Sort Utility: z/Os Host DF Sort
proc sort data = work.Source
out = work.Target
NOEQUALS;
by CRED_NO DBTR_GRP_NO SEC_NO CUST_NO REC_ID REC_TYP TRANS_CD TXT_TYPE_ID REC_NO;
run;

Time Taken: 30 mins!!

Can some one please suggest how can I reduce this time.

Re: Performance issues in sorting Large Data Set

Robert_Bardos — Thu, 12 Aug 2010 14:21:03 GMT

Please post the log messages as well. Considering the options you have set, you should have a plenty of them. Be sure to specify option SORTMSG as well.

Re: Performance issues in sorting Large Data Set

Doc_Duke — Thu, 12 Aug 2010 19:17:11 GMT

Pratik,

You will probably have to experiment with this as there are so many variables and some of them a host specific.

Try the TAGSORT option; that can sometimes speed up a sort by reducing disk I/O.

Unless you have text with lots of white-space, the compress=yes can actually slow down SAS.

z/OS installations often use non-SAS sort tools (we used SyncSort years ago). You may be able to speed it up by passing parameters directly to the tool. Check SAS's z/OS companion.

Another possibility is to use PROC SQL. I don't know that it would speed it up, but do know that it uses different SAS routines.

Doc Muhlbaier
Duke