topic Re: Performance optimization on select query for all entries in Application Development and Automation Discussions

Performance optimization on select query for all entries

Former Member — Tue, 01 Jun 2010 12:35:57 GMT

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.

Re: Performance optimization on select query for all entries

Former Member — Tue, 01 Jun 2010 13:19:12 GMT

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

Re: Performance optimization on select query for all entries

HermannGahm — Tue, 01 Jun 2010 13:22:36 GMT

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

Re: Performance optimization on select query for all entries

Former Member — Tue, 01 Jun 2010 14:16:38 GMT

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.

Re: Performance optimization on select query for all entries

Former Member — Tue, 01 Jun 2010 15:50:48 GMT

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?

Re: Performance optimization on select query for all entries

Former Member — Tue, 01 Jun 2010 16:16:13 GMT

Moderator message - Please see before posting - post locked Rob