Date:         Mon, 21 Dec 2009 05:09:36 -0800
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: PROC SORT performance difference with the WHERE clause
Comments: To: RolandRB <rolandberry@hotmail.com>
In-Reply-To:  <021622b2-8c58-4b02-9d56-7a41b31ef31c@r24g2000yqd.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1

Roland,

Which one will run faster, I think, is simply dependent upon which one
runs first.  I don't think that it has anything to do with the fact
that your data came from Oracle .. you can get the same or opposite
results using an expanded version of sashelp.class.

Run each sort 10 times in a row and I think that you'll find the same
discrepancy within each method.

Interestingly, the CPU time required stays almost constant and the
entire difference occurs outside of CPU.

Art
-----------
On Dec 21, 5:58 am, RolandRB <rolandbe...@hotmail.com> wrote:
> I have just come across a situation where PROC SORT performance is 10
> times faster if the WHERE claase is applied to the PROC SORT step
> rather than applied to the input dataset. In other words:
>
> PROC SORT DATA=in OUT=OUT;
> BY vara varb;
> WHERE condition;
> RUN;
>
> ....works 10 times faster than.....
>
> PROC SORT DATA=in(WHERE=(condition)) OUT=OUT;
> BY vara varb;
> RUN;
>
> ....for exactly the same WHERE condition creating exactly the samer
> number of observations in the output dataset. I am talking one minute
> compared to 10 minutes for the elapsed sort time. In both cases I
> think the data is coming from an ORACLE table. Is there an explanation
> for this difference in performance that I could read about someplace?