topic Re: Issue with select statement which is not using index in Application Development and Automation Discussions

Issue with select statement which is not using index

Former Member — Wed, 03 Dec 2008 06:02:28 GMT

Hi,

SELECT vgbel

FROM vbak INTO TABLE t_order

FOR ALL ENTRIES IN t_contract

WHERE auart IN r_odrtyp

AND vgbel = t_contract-vbelv.

The above select statement is causing a performance issue because it is not using an index.If the index VBAK-AUD which is having AUDAT field is used in the where condition of the select statement the performance can be improve.

Is it feasible to use audat field in the where condition?By using this field in the where condition will filter the entries from vbak table still further?

Re: Issue with select statement which is not using index

Former Member — Wed, 03 Dec 2008 06:26:32 GMT

Hi,

Remember when ever using for all entries,

always select the key fields when selecting using for all entries.

eg:

select key1 key2 .....keyn

from vbak into it_vbak

for all entries in it_vbap

where conditions...

Thanks & Regards,

Krishna...

Re: Issue with select statement which is not using index

h_senden2 — Wed, 03 Dec 2008 06:44:34 GMT

Create your own index for table VBAK in transaction SE11.

regards,

Hans

Re: Issue with select statement which is not using index

rainer_hbenthal — Wed, 03 Dec 2008 07:14:54 GMT


>     Remember when ever using for all entries, 
> always select the key fields when selecting using for all entries.
> eg:
> select key1 key2 .....keyn
> Krishna...

Does modifying the select clause influences the optimizer in the evaluation of the access path?

Re: Issue with select statement which is not using index

ThomasZloch — Wed, 03 Dec 2008 08:56:34 GMT

Try using table VBFA, it stores the link of predecessor and successor document that you seem to be looking for here.

Thomas

Re: Issue with select statement which is not using index

Former Member — Thu, 04 Dec 2008 08:36:54 GMT


> Hi,
> 
> SELECT vgbel
>            FROM vbak INTO TABLE t_order
>            FOR ALL ENTRIES IN t_contract
>            WHERE auart IN r_odrtyp
>            AND vgbel = t_contract-vbelv.
> 
> The above select statement is causing a performance issue because it is not using an index.If the index VBAK-AUD which is having AUDAT field is used in the where condition of the select statement the performance can be improve.
> 
> Is it feasible to use audat field in the where condition?By using this field in the where condition will filter the entries from vbak table still further?

Hi,

why do you think it should use an index?

If you retrieve a significant fraction of the table rows it might skip single index lookups in favour for a full index or table scan.

An ST05 trace / execution plan could help:

With the FAE entries construct you step into problems when t_contract contains a lot of entries - especially

if no index is used:

bye

Re: Issue with select statement which is not using index

Former Member — Thu, 04 Dec 2008 11:46:59 GMT

Hi Yukon,

If the select statement uses any index from vbak table,then in that case can we use FAE even if the for all entries internal table is having many records?

Re: Issue with select statement which is not using index

Former Member — Thu, 04 Dec 2008 13:26:24 GMT

An Index will be of use if you retrieve a small set of rows (~ 2 - 5%) AND if the field(s) in your SELECT list are part of the index key. In that case we avoid an additonal lookup in the table to get the non-index fields data. It would make the FAE part more acceptable if it had many entries.

The problem with FAE is that if you don't have an appropriate index it will full scan your table

several times because the internal FAE processing will transform your single SQL in several statements wich combine the fields with OR's wich inturn make it harder to get an efficient SQL execution plan.

It's really important to keep the entries small in FAE!

For this reason FAE can never be a replacement for a Join operation if you deal with big tables

Therefor I prefer joins if applicable, because IF things go worse you scan the table only once, and if an index would be supported it would also be faster because you run only 1 statement.

I would encourage you to use ST05 to trace your SQL and you will see HOW FAE is translated to several statements send to the database- it's an eye opener behind the scenes of OPEN SQL.

bye