topic Re: Optimize Crazy Select Statement with Left Join in Application Development and Automation Discussions

Optimize Crazy Select Statement with Left Join

Former Member — Thu, 09 Aug 2012 18:45:21 GMT

Hi,

I have a crazy select statement that I would like to optimize, it uses many Left Joins. Perhaps, breaking them into multiple select statements will improve performance? For simplicity, here is a subset of the select:

SELECT p~aufnr 
                 p~vornr 
                 c~vornr 
                  i~strno
                 etc...
     FROM custom_table AS p
     LEFT JOIN iflos AS i ON i~tplnr = p~tplnr
     LEFT JOIN afvc AS c ON c~aufpl = p~aufpl
     etc...
     APPENDING CORRESPONDING FIELDS OF TABLE i_data
     WHERE p~aufnr = order_list-aufnr
            AND p~autyp IN r_autyp
            AND etc...

Thanks in advance!

Re: Optimize Crazy Select Statement with Left Join

Former Member — Thu, 09 Aug 2012 20:16:26 GMT

Are you planning to use for all entries by breaking the select query. It may or may not bring the improvement. There are multiple discussions on joins vs for all entries. Read them to get some insight. And finally perform a real world stress test to see which one is better - preferably in a quality system where data is similar to the volumes in production.

Also please try not to use the corresponding fields addition.

Regards,

Shravan

Re: Optimize Crazy Select Statement with Left Join

Former Member — Thu, 09 Aug 2012 21:00:10 GMT

I was thinking of replacing the LEFT JOIN with SELECT SINGLE in a LOOP, but not sure how?

Re: Optimize Crazy Select Statement with Left Join

Clemenss — Thu, 09 Aug 2012 21:10:33 GMT

Hi Tim,

1. Do not use any ALIAS. Instead use i.e.

iflos~tplnr

afvc~aufpl

etc.

This gives a boost to readability and makes the JOIN much less crazy

2. Make sure you have as many primary or secondary index fields in the join conditions as possible. If you join on a field that has no database index (or is not the first field in the index) this will give bad performance.

Rather consider creating a database index where needed.

If all join conditions use index-based access (check ST05!) you won't get better performance regardsless what you do and regardless what self-declared experts may tell you.

Regards

Clemens

Re: Optimize Crazy Select Statement with Left Join

Former Member — Fri, 10 Aug 2012 03:40:49 GMT

Hi,

You should avoid multiple Joins in a select and also avoid using select in a loop.

Better divide the the current select statement into two select statements using for all entries or joins i.e., one select with i~tplnr = p~tplnr and other select with

c~aufpl = p~aufpl using for all entries or join.

and also Instead of select inside a loop.consider select outside the loop and get the records into a Internal table and inside loop use Read statement over the fetched Internal table, this will help in better performance.

Regards,

Ramana

Re: Optimize Crazy Select Statement with Left Join

HermannGahm — Tue, 14 Aug 2012 07:16:28 GMT

Hi,

1.) stopp all guesswork

2.) leave the statement as it is

3.) analyze the perf problem by

a.) taking an ST05 trace

b.) analyzing the execution plan

4.) try to understand the problem in the plan

5.) fix that problem

don't guess, don't workaround on the ABAP layer until you found the problem on the DB layer and tried to fix the problem on the DB layer.

If your car would go to slow or not as fast as expected you would not start with making random

changes to it e.g. just refill oil or change tyres would you? You would first analyze the problem... (brakes not clsed, enough air in tyres, check oil, ...)

Kind regards,

Hermann