Select statement particularly slows

[siongchao_ng]

Hi all,

Following is the statement that managed to get 6 entries from the database tables. However it seems very slow and roughly takes about 15 seconds to retrieve the data. Anyone have any idea what is exactly wrong with this select statement?

   select DISTINCT a~FKNUM
                  a~STABR
                  b~TDLNR
                  b~NETWR
                  b~WAERS
                  b~rebel
                  b~EBELN
                  b~EBELP
                  c~EXTI2
                  c~TKNUM
                  c~shtyp
                  c~tplst
                  d~vbeln
                  e~matnr
                  e~ARKTX
                  e~WERKS
                  f~INCO2
                  f~NTGEW
                  c~daten
                  g~name1
                  h~knumv
*                  h~kposn
INTO CORRESPONDING FIELDS OF TABLE lt_shipment_cst_final
from VFKK as a
inner join vfkp as b
on a~FKNUM = b~FKNUM
and a~mandt = b~mandt
inner join vttk as c
on c~tknum = b~REBEL
and c~mandt = b~mandt
inner join vttp as d
on c~tknum = d~tknum
and c~mandt = d~mandt
inner join LIPS as e
on d~vbeln = e~vbeln
and d~mandt = e~mandt
inner join LIKP as f
on e~vbeln = f~vbeln
and e~mandt = f~mandt
inner join LFA1 as g
on b~TDLNR = g~LIFNR
AND b~mandt = g~mandt
left outer join VFSI as h
on h~KNUMV = b~KNUMV
AND h~mandt = b~mandt
*where a~mandt = '101'
WHERE e~vbelv > '0'
AND   b~tdlnr in s_trans
AND   a~stabr in s_stabr
AND   c~shtyp in s_stype
AND   c~tplst in s_tplan
AND   c~daten ge p_from
AND   c~daten le p_to.

[Former Member]

This message was moderated.

[siongchao_ng]

Hi Pavan,

In this case:

1. Since I created my own internal table with various fields from various database tables. I have to use INTO CORRESPONDING TABLE.

2. There is no prior internal tables I used so basically there is no data to depend on for selection. FOR ALL ENTRIES no applicable in this case.

3. There is entries after select so sy-subrc n/a here

4. same as no.2

[matt]

The previous posters advice was wrong, and I have rejected his posting for promoting the myth that FAE is somehow better.

[former_member209120]

Hi,

As for the performance basis it is not suggested to use more than 3 (4 MAX) joins at a time in a single select statement.

Refer this link -

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

[matt]

The opinions expressed in that link should not be taken as being authoritative.

[Former Member]

Hi Siong,

Correct the sequence of fields in where condition.

For Eg :

This

from VFKK as a

inner join vfkp as b

on a~FKNUM = b~FKNUM

and a~mandt = b~mandt

should be

from VFKK as a

inner join vfkp as b

on a~mandt = b~mandt

and a~FKNUM = b~FKNUM

and Like wise. 

Rest see Pavan's 1,2 & 4 th sugg.

BR.

[siongchao_ng]

Hi Ankit,

Changed the sequence but no avail. Sill very slow.

[Former Member]

Hi,

it may be caused by the item database tables VFKP, VTTP and LIPS.

In your sample all´data retrieved after joining LIPS will be accessed several times (same data for all LIPS items). There may be other duplicates concerning the other item tables.

Regards,

Klaus

[adam_krawczyk1]

Hi,

You created really complex statement - 8 tables in join condition and 7 conditions in WHERE part. The most time consuming is probably joining over many tables, but it might be also WHERE condition without index in particular case. If you want to try to improve query performance I would recommend you to:

• Split JOINs into separate queries, which means that you would have 8 queries against different tables.
• Measure each query duration with ST05 to see if separated queries have performance issue, or it is only when they are combined into one complex query. If single query takes long time, you can consider index adjustments there.

Splitting query into separate SELECTs may give another benefit - sometimes 8 queries for DISTINCT rows from different tables will be faster than JOIN over 8 tables and getting DISTINCT final result, try by experience.

Regards

Adam

[former_member196490]

Try the following to improve performance.

1. Instead of 'DISTINCT' use Delete adjacent duplicates after the data has been brought in

2. do not use move corresponding, instead create the internal table with the structure same as field selection

3. Try to reduce the joins on the table.There are no primary keys listed for any table in the where condition hence the join condition might take a lot of time. If possible try to increase the condition in where clause

Create seperate queries for the tables or join not more than two tables to analyze which select is taking time

[Former Member]

This message was moderated.

[UweFetzer_se38]

I like this thread a lot <sarcasm />

First to the OP:

all what and said

+ really check the purpose of the query incl. the primary keys and indexes of all the involved tables.

+ you don't need to use the "mandt" field in your query.

To the others

Would be great if we get an explanation for the following statements:

• You should not use INTO CORRESPONDING TABLE
• You should use FOR ALL ENTRIES in the select if are getting the data from more than one DB Table
• As for the performance basis it is not suggested to use more than 3 (4 MAX) joins at a time in a single select statement
• Correct the sequence of fields in where condition
• Instead of 'DISTINCT' use Delete adjacent duplicates after the data has been brought in
• The inner join should not contain more than 2 tables.Otherwise the Query performance will be  seriously affected
• .. and the order of the fields in the Where clause should be the same as in the Database Table

[matt]

To remove all doubt, Uwe is saying that these statements are WRONG.

I can say that two of the statements make no difference, one is exactly the wrong way round and the rest are complete twaddle. As an exercise, try and work out which is which.

[Former Member]

Uwe Fetzer,

In our company we believe some of this really make difference in performance.

We prefer INTO TABLE than INTO CORRESPONDING FIELDS OF TABLE.

Is there any thing wrong in these points.

• You should not use INTO CORRESPONDING TABLE
• You should use FOR ALL ENTRIES in the select if are getting the data from more than one DB Table
• Correct the sequence of fields in where condition
• .. and the order of the fields in the Where clause should be the same as in the Database Table

[matt]

What's wrong with them is that they are wrong. They are beliefs that possibly were true back in the days of 30D, but certainly are NOT true now. I find it frankly depressing that these myths continue.

• You should not use INTO CORRESPONDING TABLE

Ignoring performance for a while, INTO TABLE is more likely to end up with dumps (or if not, hard to find runtime errors) than INTO CORRESPONDING. Good programming practice means being specific. INTO TABLE is saying take data from the db table and dump it into my internal table and I hope it fits You are relying on the itab being correctly defined - this is dangerous. The itab definition changes and your program falls over (or provides corrupted data). INTO CORRESPONDING is at least ensuring that the right fields from the db go into the right fields in the internal table.

Fixing programs that fail in production is FAR MORE EXPENSIVE than possible performance gains.

• You should use FOR ALL ENTRIES in the select if are getting the data from more than one DB Table

This is simply wrong. The general rule - proven again and again - is that INNER JOIN is usually faster than FAE, and should be used wherever possible. If there are performance issues, only switch to FAE if it is demonstrably faster in the particular instance. From a coding perspective, INNER JOINS produce much neater, smaller, easier to understand code. FAE produces EXPENSIVE code - trickier to fix, harder to understand, more places to go wrong.

• Correct the sequence of fields in where condition

Twaddle. Makes NO DIFFERENCE whatsoever. Ensuring you meet this condition simply makes development more expensive.

• .. and the order of the fields in the Where clause should be the same as in the Database Table

Twaddle. Makes NO DIFFERENCE whatsoever. Ensuring you meet this condition simply makes development more expensive.

Why? Because the optimisers know what to do.

If someone insists that your company's beliefs are correct, then I suggest that you write some test programs. My experience over 16 years of ABAP development is that your company's guidelines are making development MORE EXPENSIVE. In the interest of saving money, they should do some proper research and stop perpetuating myths.

[UweFetzer_se38]

Thank you

[Former Member]

It is exactly this myth perpetuation, especially around FAE vs. joins, that means I've given up on the ABAP forums these days.

So much hearsay, so little hard fact.

"In our company we believe" - and in some places, people believe in fairies and unicorns.  Doesn't make it correct.

Gareth.

[ThomasZloch]

Don't give up, rather JOIN our FAE (fight against evil).

Thomas

[Former Member]

Thanks a lot Matthew.

[matt]

Every time someone says that FAE is better than INNER JOIN, a faerie dies.

[arindam_m]

I know myths are hard to break even when we have analysis tools in SAP people believe more in hearsay.. but please keep your efforts going (Reiterating the point in discussions)

Cheers,

Arindam

[Former Member]

That explains why there aren't any around!

[sriharsha_parayatham]

This message was moderated.

[matt]

Moderator message

I am monitoring this thread. Incorrect, misleading responses will be rejected without warning. If you want to post something on this topic, make sure your facts are correct.

[ThomasZloch]

It's difficult to spot at quick glance, but the ON-conditions look OK (except the dispensable MANDT-relations), so it seems that the response time will be highly affected by how those selection ranges are filled at runtime.

See what happens if you fill one single value each in s_tplan and s_stype (use a valid combination), as this should trigger usage of VTTK~A index. Does this return any quicker?

Thomas

P.S. please format your code better next time, and think about using actual table names rather than aliases (unless working with self-joins).

[Former Member]

 P.S. please format your code better next time, and think about using actual table names rather than aliases (unless working with self-joins).

I simply cannot understand why people use "a", "b", "c", etc as aliases for tables.  What's wrong with "mara as mara"?!