sql performance tuning

[Former Member]

Hi all,

I have an SQL query with poor performance that it runs for at least 8 hrs +, I've tried to fine-tune it in many ways, but not much improvement is found:

-

---

ranges r_equnr for afih-equnr.

ranges r_ilart for afih-ilart.

r_equnr-sign = 'I'.

r_equnr-option = 'CP'.

r_equnr-low = '00000000001*'.

append r_equnr.

r_ilart-sign = 'I'.

r_ilart-option = 'CP'.

r_ilart-low = 'A*'.

append r_ilart.

select aufnr from afih into table itab

where equnr in r_equnr and ilart in r_ilart.

select a_{aufnr a}mblnr a_{mjahr a}zeile b~budat

into corresponding fields of table rtab

from mseg as a inner join mkpf as b

on a_{mblnr = b}mblnr and a_{mjahr = b}mjahr

for all entries in itab

where a_{aufnr = itab-aufnr and b}cpudt in s_date

and ( a_{bwart = '261' or a}bwart = '262' ).

Any suggestion to greatly improve the performance?

Many Thanks.

[Former Member]

Hi,

1.First SELECT can have IPHAS( say = 2,4,6 check for the fixed values) in the WHERE condition.

2.Ensure it_tab[] IS NOT INITIAL

3.try to provide mkpf-budat also try to include matnr and werks from MSEG in the 2nd select ,WHERE cond.

Cheers

[Former Member]

1)Try to add few more conditions in where condition while getting data from AFIH,data will be large in this.

2)In second select query you need to declare one more range for Movement type(BWART) and data in MSEG and MKPF usually will be huge and it will deffinately take some time.

Hope this helps.

Thanks & Regards,

Rock.

[Former Member]

Hi Macy

Before the second select Query , there must be a check for entries in itab ..

with out that it may result for Time out dump .

select aufnr from afih into table itab

where equnr in r_equnr and ilart in r_ilart.

IF NOT itab IS INITIAL .

select a_{aufnr a}mblnr a_{mjahr a}zeile b~budat

into corresponding fields of table rtab

from mseg as a inner join mkpf as b

on a_{mblnr = b}mblnr and a_{mjahr = b}mjahr

for all entries in itab

where a_{aufnr = itab-aufnr and b}cpudt in s_date

and ( a_{bwart = '261' or a}bwart = '262' ).

ENDIF .

Apart from that .,

Why do you need that first select query there u r just fetching all AUFNR againest the Select-options .

same thing directly u can write in the second one only .

Just check wether u need that for any other purpose , if not better use select option directly

thanks

Sreenivas

[Former Member]

Thanks. The first select query is required as i want to fetch out all the service orders with MAT = AXX and with equipment no. starts with 1, so the itab must not be initial, do i still need to put the check:

'if not itab is initial.' as suggested after the first select query?

Thanks again.

[andrea_galluccio2]

Hi,

create a secondary index on the first table (MSEG), based on field that you are using to select data plus the MANDT field (as first field).

Regards

Andrea

[Former Member]

Hi Andrea,

Could you show me how to create a 2nd index, any examples?

Thanks!

[Former Member]

Hi,

try to use IN instead of 'OR'

• and ( a_{bwart = '261' or a}bwart = '262' ).

and a~bwart IN ('261','262')

[Former Member]

Hi,

use below path for creation of secondary index for a table

Go to transaction SE11, open your database table. Choose the menu, Goto->Indexes to create index. Give your index name and choose the fields of the table.

Regards,

Madhu

[Former Member]

> create a secondary index on the first table (MSEG), based on field that you are using

> to select data plus the MANDT field (as first field).

just because one select is slow, is not a sufficient argument to create an index to a central table.

How many records aree in your MSEG table?

Siegfried

[Former Member]

select aufnr from afih into table itab 
where equnr in r_equnr and ilart in r_ilart.

select a~aufnr a~mblnr a~mjahr a~zeile b~budat
into corresponding fields of table rtab 
from mseg as a inner join mkpf as b
on a~mblnr = b~mblnr and a~mjahr = b~mjahr
for all entries in itab
where a~aufnr = itab-aufnr and b~cpudt in s_date
and ( a~bwart = '261' or a~bwart = '262' ).

The whole thing is very weird.

Please check with the SE11 and table entries, how many records you have in each of these tables.

Why do you need the ranges?

r_equnr-sign = 'I'.
r_equnr-option = 'CP'.
r_equnr-low = '00000000001*'.
append r_equnr.

r_ilart-sign = 'I'.
r_ilart-option = 'CP'.
r_ilart-low = 'A*'.
append r_ilart. 

If there are not more conditions then write a simple WHERE condition with LIKE !!!

select aufnr 
         from afih 
         into table itab 
         where equnr LIKE '00000000001*'
         and     ilart   LIKE 'A*'..

And then I would go for the complete join

select a~aufnr a~mblnr a~mjahr a~zeile b~budat
          into corresponding fields of table rtab 
          from             mkpf as b
          INNER JOIN mseg as a
          on a~mblnr = b~mblnr 
          and a~mjahr = b~mjahr 
          INNER JOIN  afih as c
          on a~aufnr = c~aufnr
          where b~cpudt in s_date
            and  a~bwart  in ( '261', '262' )
            and  c~equnr LIKE '00000000001*'
            and  c~ilart   LIKE 'A*'.

There is probably one index missing

either on MKPF on cpudt

or on MSEG on aufnr

but first try without index. And tell us the size of the tables.

Siegfried