optimization of PRoc sql query

[vnarendra9]

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

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

[vnarendra9]

Hi , already these source tables have index files created.

[DBailey]

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

[RichardinOz]

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