Performance optimization on select query for all entries

[Former Member]

Hi All,

I want to optimize the select query in my Program.

The select query is taking lot of time to search the records for the given condition in the where clause

and more interestingly there are no records fetched from the database as the where condition does not matches.

It is taking more than 30 min to search the record and the result is no record found.

Below is my select query. I have also created the secondary Index for the same.

In My opinion FOR ALL ENTRIES is taking lot of time. Because there are more than 1200 records in internal table t_ajot

select banfn bnfpo bsart txz01 matnr Werks lgort matkl reswk menge meins flief ekorg

INTO CORRESPONDING FIELDS OF TABLE t_req

FROM eban

FOR ALL ENTRIES IN t_ajot

WHERE matkl >= t_ajot-matkl_low

AND matkl <= t_ajot-matkl_high

AND werks = t_ajot-werks

AND loekz = ' '

AND badat IN s_badat

AND bsart = 'NB'.

Please suggest.

[Former Member]

what Index would you use to support this WHERE clause ???

WHERE matkl >= t_ajot-matkl_low
AND matkl <= t_ajot-matkl_high
AND werks = t_ajot-werks
AND loekz = ' '
AND badat IN s_badat
AND bsart = 'NB'.

[HermannGahm]

Hi,

that,

FOR ALL ENTRIES IN t_ajot

WHERE matkl >= t_ajot-matkl_low

AND matkl <= t_ajot-matkl_high

AND werks = t_ajot-werks

AND loekz = ' '

AND badat IN s_badat

AND bsart = 'NB'.

looks strange.

However:

How does your index look like?

What executoin plan do you get?

How do the statistics look like?

Whats the content of the variables t_ajot-... and s_badata?

Kind regards,

Hermann

[Former Member]

Do you have MATNR available?

Because theres an index in eban that uses

MATNR

WERK

LOEKZ

MATKL

If you could include MATNR in this search, this index would activate and everything would work faster.

[Former Member]

There is nothing wrong with "FOR ALL ENTRIES" option, and we use it all the time. 1200 rows in t_ajot is not at all a big deal.

You said you have created a secondary index, but is it created correctly? Can you share the index fields with us?

Other questions to poder:

1) Do you know for a fact that your index is being used?

2) How many rows are there in EBAN table?

3) How many entries would be there between matkl_low and matkl_high?

[Former Member]

Moderator message - Please see before posting - post locked Rob