Hi ,
Please help me onthis.
below is proc sql query which needs to be optimized as it runs for 10 hours .
there are two large tables , largetb1(with arrround 30 million records) & largetb2(2 mililon records) .
To optimize below one , i have to earse sort action (highlighted one in below log).
how do i erase sort (sqxsort) action in below query ?
--------------------------------------------------------------------------
818 proc sql noprint _method ;
819 create table svc03 as
820 select s.*,
821 c.v1, p.v5,p.v6
822 from qa20.largetb1 s
826 join qa20.largetb2(keep= v1 v2 v3) c
827 on s.v1= c.v1
828 left join smalltb1 p
829 on s.key= p.key;
NOTE: SQL execution methods chosen are:
sqxcrta
sqxjm
sqxsort
sqxsrc( AA.PROVSPEC1(alias = P) )
sqxsort
sqxjm
sqxsrc( QA20.CLAIMS(alias = C) )
sqxsort
sqxsrc( QA20.SVCLINES(alias = S) )
-----------------------------------------------------------------------------------------------------------
Hi , already these source tables have index files created.
Here's a good treatment of potential optimization methods:
http://www2.sas.com/proceedings/sugi30/101-30.pdf
Have you tried rewriting or splitting the execution into two steps (the inner join and the left join)?
From what we can see the large tables are likely to be located in an enterprise RDBMS and to follow on DBailey's suggestion you might consider using explicit passthrough on the inner join as an uncorrelated subquery, and perform the left join on the SAS side.
Do you really need all the variables from largetb1 in your result set? Reducing the size of the output vector should improve write performance (eg key is probably redundant after the left join is performed). Why do you select both s.vi (implied) and c.v1 if they are there as a result of an equi-join? And if SAS must perform the SQL, why keep v2 and v3 from largetb2 if they play no part in the logic and the output?
We can't tell from the information whether the output dataset has 30 million records (a many to one join), 2 million records (one to one, excluding non matches), or some other number. The SAS optimiser has evidently determined that sort merges are required (ie it will not use the indexes). You might be able to avoid two of the sorts by creating formats from the small table.
Richard
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.