SAS SQL relies on the sort routines being used by the SAS System. Some
installations of the SAS System use SyncSort. So far as I can tell, SyncSort
generally works faster than other sort routines, although in many
applications the differences are small.
From: Alexander Cavallo [mailto:acavallo@LEXECON.COM]
Sent: Monday, February 02, 2004 6:07 PM
Subject: SQL speed
I am new to using SQL. From reading SAS-L, I thought that SQL sorts faster
than SYNCSORT. In the example below, SYNCSORT is faster. Could you explain
to me how big a dataset needs to be before SQL is faster?
48 proc sql noprint;
49 create table temp.sql as (
50 select distinct emp_id, date
51 from hrdata.all )
52 order by emp_id, date
NOTE: Table TEMP.SQL created, with 179291 rows and 2 columns.
NOTE: The PROCEDURE SQL used 2.22 CPU seconds and 10730K.
56 proc sort nodupkey data=hrdata.all out=temp.test(keep=emp_id
57 by emp_id date;
NOTE: WER750I End PROC SYNCSORT. R2.2D+
NOTE: There were 179291 observations read from the data set HRDATA.ALL.
NOTE: The data set TEMP.TEST has 179291 observations and 2 variables.
NOTE: The PROCEDURE SORT used 1.88 CPU seconds and 10907K.