Performance SQL vs MERGE

[wildcat]

Hello,

   I've made some tests for merging 2 tables (inner join), one having around 13 million observations, and the other one with 300.000, and I have received the following time results, within the 3 different scenarios:

    1) PROC SQL   -> 20 minutes;

    2) MERGE with sort on BY variable -> 1h and a half including sorting on tables;

    3) MERGE with indexes on BY variable (no sorting) -> 30 hours.

  So my questions are:

   - Why PROC SQL is more efficient than MERGE;

   - Why MERGE without a previous sort is 30 times more time consuming than MERGE with sort. I know that sorting is reccomended before merge, but the performance decrease is really significant in this case.

I would appreciate your answers very much, thanks a lot.

[sascom10]

Are you performing proc sql join as in-database query. Cause if at all thats the case then it makes whole lot of difference as the db engine will handle the execution of sql as opposed to sas.

cheers

[Astounding]

One key issue:  did you run the PROC SORT before running SQL?  If so, it's not really a fair comparison.  SQL will change its joining method to take advantage of the sorted order.

Regarding outcome #3, you have to picture how SAS extracts observations.  It moves an entire page of data into memory, then extracts the observations from memory.  When you use an index, most of the work is swapping pages into memory to get the right page that contains the next observation.  Don't use an index to read every observation!  Ian Whitlock once wrote a short, related paper called something like "Why Did This Code Take 24 Hours to Run?"

Good luck.