topic Re: sql performance tuning in Application Development and Automation Discussions

sql performance tuning

Former Member — Mon, 16 Mar 2009 06:53:22 GMT

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.

Re: sql performance tuning

Former Member — Mon, 16 Mar 2009 07:42:26 GMT

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

Re: sql performance tuning

Former Member — Mon, 16 Mar 2009 07:57:06 GMT

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.

Re: sql performance tuning

Former Member — Mon, 16 Mar 2009 09:06:50 GMT

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

Re: sql performance tuning

Former Member — Mon, 16 Mar 2009 14:55:58 GMT

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.

Re: sql performance tuning

andrea_galluccio2 — Mon, 16 Mar 2009 16:29:19 GMT

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

Re: sql performance tuning

Former Member — Tue, 17 Mar 2009 00:52:19 GMT

Hi,

try to use IN instead of 'OR'

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

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

Re: sql performance tuning

Former Member — Tue, 17 Mar 2009 01:54:15 GMT

Hi Andrea,

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

Thanks!

Re: sql performance tuning

Former Member — Tue, 17 Mar 2009 14:06:39 GMT

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

Re: sql performance tuning

Former Member — Tue, 17 Mar 2009 14:12:03 GMT

> 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

Re: sql performance tuning

Former Member — Tue, 17 Mar 2009 14:41:35 GMT


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