topic Re: optimization of PRoc sql query in SAS Programming

optimization of PRoc sql query

vnarendra9 — Wed, 22 May 2013 12:16:40 GMT

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) )

-----------------------------------------------------------------------------------------------------------

Re: optimization of PRoc sql query

vnarendra9 — Wed, 22 May 2013 13:08:47 GMT

Hi , already these source tables have index files created.

Re: optimization of PRoc sql query

DBailey — Wed, 22 May 2013 13:25:19 GMT

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)?

Re: optimization of PRoc sql query

RichardinOz — Thu, 23 May 2013 11:19:56 GMT

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