Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

How to speed up select with join ?

Former Member
0 Kudos

Hi

I 'd like to do a selection into Purchasing table EKKO/EKPO/EKBE.

I am searching how I can optimize this select :

If someone have tips and tricks, on the way to speed up my selection ? I've tried to change the order of the table in the From but the response time doesn't really change.

This is my code :

select ekbe~belnr

ekbe~bewtp

ekbe~budat

ekbe~menge

ekbe~dmbtr

ekbe~shkzg

ekbe~lfbnr

ekbe~matnr

ekbe~werks

ekpo~ebeln

ekpo~ebelp

ekpo~matkl

ekpo~meins

ekko~lifnr

ekko~ekorg

ekko~ekgrp

ekko~waers

into table t_ekpo

from ( ekbe inner join ekpo

on ekbeebeln eq ekpoebeln

and ekbeebelp eq ekpoebelp )

inner join ekko

on ekbeebeln eq ekkoebeln

for all entries in t_ekbe

where ekbe~ebeln eq t_ekbe-ebeln

and ekbe~ebelp eq t_ekbe-ebelp

and ekbe~lfbnr eq t_ekbe-belnr

and ( ekbe~bewtp eq 'E'

or ekbe~bewtp eq 'R' )

and ekbe~budat in s_sptag

and ekpo~matkl in s_matkl

and ekpo~matnr in s_matnr

and ekko~lifnr in s_lifnr

and ekko~ekgrp in s_ekgrp

and ekko~ekorg in s_ekorg.

Hope you could help me.

Regards,

Erwan.

1 ACCEPTED SOLUTION

ulrich_koch
Explorer
0 Kudos

Hi Erwan,

joining in the logical order "ekko join ekpo join ekbe" might be a step in the right direction. That depends on how smart the optimizer is. Another point is the join of ekpo and ekbe: better use ekpoebeln and ekpoebelp and not ekko~ebeln. Again, may be the optimizer is not smart enough. Then have a look at the SQL trace (tx ST05) whether the primary index is used for all three tables. If not: try to add the client condition to the ON conditions:

from ekko

inner join ekpo

on ekkomandt = ekpomandt and

ekkoebeln eq ekpoebeln

inner join ekbe

on ekbemandt = ekpomandt and

ekbeebelnp = ekpoebeln and

ekpeebelp = ekpoebelp

And leave the select-options where they are. The database should know as much as possible in order not to deliver too many records to the application server.

Best regards

Ulrich

6 REPLIES 6

VXLozano
Active Contributor
0 Kudos

If your selection will return you few records (less than 1000, for example), I think you will do it faster without joins.

Just use your WHERE clause to populate internal tables, it will be faster, I guess.

Former Member
0 Kudos

Hi,

I suggest creating a maintainence view with these tables and then selecting the particular fields into an internal table .This wud result in better performance.Try this out.

Regards

Abhishek

nablan_umar
Active Contributor
0 Kudos

Erwan,

Try use the header table as the main SELECT since the system will scan less record if you do header first. I suggest you do this

select ekbe~belnr

ekbe~bewtp

ekbe~budat

ekbe~menge

ekbe~dmbtr

ekbe~shkzg

ekbe~lfbnr

ekbe~matnr

ekbe~werks

ekpo~ebeln

ekpo~ebelp

ekpo~matkl

ekpo~meins

ekko~lifnr

ekko~ekorg

ekko~ekgrp

ekko~waers

into table t_ekpo

from ekko

inner join ekpo

on ekpoebeln eq ekkoebeln

inner join ekbe

on ekbeebeln eq ekkoebeln

and ekbeebelp eq ekpoebelp

for all entries in t_ekbe

where ekko~ebeln eq t_ekbe-ebeln

and ekpo~ebelp eq t_ekbe-ebelp

and ekbe~lfbnr eq t_ekbe-belnr

and ( ekbe~bewtp eq 'E'

or ekbe~bewtp eq 'R' )

and ekbe~budat in s_sptag

and ekpo~matkl in s_matkl

and ekpo~matnr in s_matnr

and ekko~lifnr in s_lifnr

and ekko~ekgrp in s_ekgrp

and ekko~ekorg in s_ekorg.

Former Member
0 Kudos

Erwan,

Why do you need to select from EKBE if you already have data in the internal Table T_EKBE(you are using FOR ALL ENTRIES in T_EKBE)? Using 'FOR ALL ENTRIES' in a 'Join' could be a performace dog, esp. with History tables like EKBE.

Thanks,

Bala

0 Kudos

Erwan,

Also, remove all 'Select Option Variables' from the WHERE clause. Using IN in WHERE clause might result into an performace issue. Deal with it after you finish selecting data.

Later,

Bala

ulrich_koch
Explorer
0 Kudos

Hi Erwan,

joining in the logical order "ekko join ekpo join ekbe" might be a step in the right direction. That depends on how smart the optimizer is. Another point is the join of ekpo and ekbe: better use ekpoebeln and ekpoebelp and not ekko~ebeln. Again, may be the optimizer is not smart enough. Then have a look at the SQL trace (tx ST05) whether the primary index is used for all three tables. If not: try to add the client condition to the ON conditions:

from ekko

inner join ekpo

on ekkomandt = ekpomandt and

ekkoebeln eq ekpoebeln

inner join ekbe

on ekbemandt = ekpomandt and

ekbeebelnp = ekpoebeln and

ekpeebelp = ekpoebelp

And leave the select-options where they are. The database should know as much as possible in order not to deliver too many records to the application server.

Best regards

Ulrich