Regarding Performance Tuning of Query

[Former Member]

HI All,

I have join already in place which is taking lots of time. Can anybody tell me how can i split the same and functionality doen't changes.

The <b>SELECT</b> Statement is as given below:

<b> SELECT A_{AUFNR A}AUART A_{ERDAT A}ERNAM B_{GSTRI A}KDAUF A~KDPOS

A_{KTEXT A}PSPEL A_{OBJNR A}ZZENGINE_MODEL A_{ZZBWTAR A}ZZNEWMATNR

A_{ZZMATNR A}ZZQTY A_{ZZQMNUM B}GLTRP B_{GSTRP B}GSTRS

B~GLTRS

B_{AUFPL B}RSNUM B_{LEAD_AUFNR C}APLZL C_{VORNR C}ARBID

C_{BANFN C}BNFPO C_{RUECK D}PRIOK

FROM ( ( AUFK AS A INNER JOIN AFKO AS B

ON A_{AUFNR = B}AUFNR ) INNER JOIN AFVC AS C

ON B_{AUFPL = C}AUFPL ) INNER JOIN AFIH AS D

ON A_{AUFNR = D}AUFNR

INTO TABLE I_SMORDER

WHERE A~ZZMATNR IN S_MATNR

AND (I_SELECT)

AND B~GLTRP IN S_ERDAT.</b>

Can anybody tell me how can i improve the query without changing the functionality.

Any help in this regard will be appreciated.

Thanks in advance.

Thanks & Regards,

Prasad.

[Former Member]

Prasad,

You can try using the view VIAUFKST for AUFK, AFKO, AFIH tables and then join that with AFVC table.

Regards,

Ravi

Note : Please mark the helpful answers

[Former Member]

HI

GOOD

YOU CHECK WITH YOUR JOINING QUERY FIRST YOU GIVE ALL THE FIELD OF THE TABLE THAT REPRESENTED BY A THAN YOU GIVE ALL THE FIELD NAME FORM THE TABLE THAT REPORESENT BY B YOU HAVE WRITTEN B VALUE UNDER A VALUE AND THAT MIGHT BE THE REASON OF LATE ACCESSING THE DATA.

THANKS

MRUTYUN

[Former Member]

Hi Prasad

First of all, while joins make for neater code they tend to be inefficient. I believe it is better to select from 1 database table at a time and use the FOR ALL ENTRIES option to produce the same result as the joins i.e.

select from aufk into itab1 where...

then

select from afko into itab2 for all entries where aufnr = itab2-aufnr

and so on...

Secondly, in the Where clause you should use as much of the primary key (beginning with the first key field and moving progressively to the right) as you possibly can. Failing that find out if there are additinoal indices on your tables and use those.

Hope that points you in the right direction - also do a search for 'performance tuning' on the forum for other similar threads.

Kind regards

Andy

[Former Member]

hi,

is i_smorder table has the same fields which u r selecting. or use corresponig fields

try it once.

and give me the reply

byeee

srinu

[Former Member]

Hi prasad,

its better to use select statement for the first db table

as andrew suggested. Later on select the required fields from corresponding tables using for all entries.

Award points if helpful.

regards,

keerthi.

[Former Member]

Hai Prasad

Instead of Inner Join you need to use For All Entries

Key word to improve the Performence

Check the following Code Change

Tables : AUFK,

AFKO,

AFVC,

AFIH.

data : begin of it_aufk occurs 0,

AUFNR like aufk-AUFNR,

AUART like aufk-auart,

ERDAT like aufk-erdat,

ERNAM like aufk-ernam,

GSTRI like aufk-gstri,

KDAUF like aufk-kdauf,

KDPOS like aufk-kdpos,

KTEXT like aufk-ktext,

PSPEL like aufk-pspel,

OBJNR like aufk-objnr,

ZZENGINE_MODEL like aufk-ZZENGINE_MODEL,

ZZBWTAR like aufk-ZZBWTAR,

ZZNEWMATNR like aufk-ZZNEWMATNR,

ZZMATNR like aufk-ZZMATNR,

ZZQTY like aufk-zzqty,

ZZQMNUM like aufk-zzqmnum,

end of it_aufk.

data : begin of it_afko occurs 0,

GLTRS like afko-GLTRS,

AUFPL like afko-AUFPL,

RSNUM like afko-rsnum,

LEAD_AUFNR like afko-LEAD_AUFNR,

end of it_afko.

data : begin of it_AFVC occurs 0,

APLZL like AFVC-APLZL

VORNR like AFVC-VORNR,

ARBID like AFVC-ARBID,

BANFN like AFVC-BANFN,

BNFPO like AFVC-BNFPO,

RUECK like AFVC-RUECK,

end of it_AFVC.

data : begin of it_AFIH occurs 0,

PRIOK like AFIH-PRIOK,

end of it_AFIH.

clear : it_aufk.

refresh : it_aufk.

select AUFNR

AUART

ERDAT

ERNAM

GSTRI

KDAUF

KDPOS

KTEXT

PSPEL

OBJNR

ZZENGINE_MODEL

ZZBWTAR

ZZNEWMATNR

ZZMATNR

ZZQTY

ZZQMNUM

from aufk

into table it_aufk

where ZZMATNR IN S_MATNR.

if not it_aufk[] is initial.

clear : it_afko,it_AFIH.

refresh : it_afko,it_AFIH.

select GLTRS

AUFPL

RSNUM

LEAD_AUFNR

from afko

into table it_afko

for all entries in it_aufk

where AUFNR = it_aufk-AUFNR

and GLTRP IN S_ERDAT.

if not it_afko[] is initial.

clear : it_AFVC.

refresh : it_AFVC.

select APLZL

VORNR

ARBID

BANFN

BNFPO

RUECK

from AFVC

into table it_AFVC

for all entries in it_afko

where AUFPL = it_afko-AUFPL

endif.

select PRIOK

from afih

into table it_AFIH

for all entries in it_aufk

where AUFNR = it_aufk-AUFNR.

endif.

Thanks & Regards

Sreenivasulu P

Message was edited by: Sreenivasulu Ponnadi

[Former Member]

Prasad - you haven't shown what what's in I_SELECT. So it's hard to say for certain if this is your problem, but I don't see any primary key fields in the rest of the where clause; so unless they're in I_SELECT, I think your problem is a select without a key field.

Rob