topic Re: Select statement particularly slows in Application Development and Automation Discussions

Select statement particularly slows

siongchao_ng — Tue, 25 Jun 2013 06:13:08 GMT

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.

Re: Select statement particularly slows

Former Member — Tue, 25 Jun 2013 06:18:42 GMT

This message was moderated.

Re: Select statement particularly slows

former_member209120 — Tue, 25 Jun 2013 06:19:55 GMT

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

Re: Select statement particularly slows

Former Member — Tue, 25 Jun 2013 06:43:04 GMT

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.

Re: Select statement particularly slows

Former Member — Tue, 25 Jun 2013 06:45:13 GMT

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

Re: Select statement particularly slows

adam_krawczyk1 — Tue, 25 Jun 2013 06:48:09 GMT

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

Re: Select statement particularly slows

siongchao_ng — Tue, 25 Jun 2013 07:44:21 GMT

Hi Ankit,

Changed the sequence but no avail. Sill very slow.

Re: Select statement particularly slows

siongchao_ng — Tue, 25 Jun 2013 07:47:48 GMT

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

Re: Select statement particularly slows

former_member196490 — Tue, 25 Jun 2013 08:03:52 GMT

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

Re: Select statement particularly slows

Former Member — Tue, 25 Jun 2013 10:00:08 GMT

This message was moderated.

Re: Select statement particularly slows

UweFetzer_se38 — Tue, 25 Jun 2013 11:12:41 GMT

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

Re: Select statement particularly slows

sriharsha_parayatham — Tue, 25 Jun 2013 11:26:09 GMT

This message was moderated.

Re: Select statement particularly slows

matt — Tue, 25 Jun 2013 12:05:00 GMT

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

Re: Select statement particularly slows

matt — Tue, 25 Jun 2013 12:06:12 GMT

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

Re: Select statement particularly slows

matt — Tue, 25 Jun 2013 12:12:28 GMT

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.

Re: Select statement particularly slows

matt — Tue, 25 Jun 2013 12:13:28 GMT

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.

Re: Select statement particularly slows

Former Member — Wed, 26 Jun 2013 06:09:05 GMT

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

Re: Select statement particularly slows

matt — Wed, 26 Jun 2013 06:35:43 GMT

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.

Re: Select statement particularly slows

UweFetzer_se38 — Wed, 26 Jun 2013 08:05:09 GMT

Thank you

Re: Select statement particularly slows

ThomasZloch — Wed, 26 Jun 2013 10:57:03 GMT

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