DISK Read

[Former Member]

we are getting very high disk read in some of our Queries but after seeing those queries there is nothing to optimise in these, but our BASIS people says that these queries are tking high disk reads, can any one sugges the possible way how to resuce the disk reading in those queries

abhishek suppal

[former_member221770]

Can you tell us more about these queries? It's a bit difficult to diagnose this issue without any examples. From the top of my head, maybe you could create some indexes for your tables?

But if you could tell us more about your queries then maybe we can give you some more specific ideas.

Cheers,

Pat.

[Former Member]

dare Pat,

as reqd. by u, pl. see this these are the few queries...

SELECT SUM( gmnga ) INTO v_gmnga

FROM afru

WHERE budat = p_date AND

ile02 = 'KWH' AND

aufnr = i_ord-aufnr AND

stokz <> 'X' AND

stzhl = init_stzhl.

********************************************

SELECT prueflos merknr vorglfnr masseinhsw verwmerkm

INTO CORRESPONDING FIELDS OF TABLE i_qamv

FROM qamv

FOR ALL ENTRIES IN i_prueflos

WHERE prueflos = i_prueflos-prueflos AND

verwmerkm IN s_verw.

********************************************

SELECT a_{bktxt b}mblnr b_{mjahr b}zeile b_{matnr b}bwart

d_{maktx b}lifnr e_{name1 b}erfmg b_{dmbtr b}lfbnr

b_{lfbja b}lfpos b_{sgtxt b}smbln b_{sjahr b}smblp

a_{budat b}werks

FROM mkpf as a

INNER JOIN mseg as b ON

a_{mblnr = a}mblnr AND

b_{mjahr = b}mjahr

INNER JOIN mara as c ON

b_{matnr = c}matnr

INNER JOIN makt as d ON

c_{matnr = d}matnr

INNER JOIN lfa1 as e ON

b_{lifnr = e}lifnr

INTO CORRESPONDING FIELDS OF TABLE I_DATA

WHERE a~budat IN s_budat AND

a~mblnr IN s_mblnr AND

a~mjahr IN s_mjahr AND

b~werks IN s_werks AND

b~lifnr IN s_lifnr AND

b~bukrs IN s_bukrs AND

b~matnr IN s_matnr AND

b~bwart IN r_bwart AND

b~matnr IN s_matkl AND

d~spras = sy-langu AND

e~spras = sy-langu.

abhishek suppal

[former_member221770]

Abhi,

Wow...you have some interesting queries there...

ok, one at a time...

first one:

select sum( afru~gmnga ) into v_gmnga

from afko

inner join afvc

on afko_{aufpl = afvc}aufpl

inner join afru

on afvc_{rueck = afru}rueck and

afvc_{rmzhl = afru}rmzhl

afko~aufnr = i_ord-aufnr and

afru~budat = p_date and

afru~ile02 = 'KWH' and

afru~stokz <> 'X' and

afru~stzhl = init_stzhl.

Give this a try. Since you know the Prod Order number, I thought it would be better to hit AFKO first as AUFNR is the Primary Key top this table. Now AFVC is the Prod Order Operation and AUFPL is the first field in it's Primary Key. Now AFVC has the Primary Key to AFRU. Now whether this actually 100% works or not is dependent on how your config is set up, but as I have tried to hit the primary keys whenever possible hopefully the performance should improve. You may want to open up a few sessions with SE16 and verify my joins as well...

Let me know how this goes...

Cheers,

Pat.

[former_member221770]

Abhi,

The I would try and split the second statement upa bit.

Something like:

SELECT prueflos merknr vorglfnr masseinhsw verwmerkm

INTO CORRESPONDING FIELDS OF TABLE i_qamv

FROM qamv

FOR ALL ENTRIES IN i_prueflos

WHERE prueflos = i_prueflos-prueflos.

delete i_prueflos not verwmerkm IN s_verw.

I suggest this because PRUEFLOS is the first part of the key, and if you only provide this it will force the DB optimiser to use the primaty key.

Pat.

[former_member221770]

Abhi,

OK, now this third statement is too big. I would recommend splitting it up a bit. Mainly because MKPF and MSEG are so big it scares me when we have to join them! There is no right or wrong way to hit these tables so you may have to play around a bit, but here are some thoughts:

First, let's assume that because you have a select option for MBLNR:

select mkpf~......

into table...

from mkpf

inner join mseg

on mkpf_{mblnr = mseg}mblnr and

mkpf_{mjahr = mseg}mjahr

where mkpf~mblnr in s_mblnr and

mpkp~mjahr in s_mjahr.

• note, i did not use "into corresponding fields of table..." because "into table..." is more efficiient as "corresponding fields" checks each and every field name to make sure that they match, while "into table" simply assumes that the fields are in the same order in the table.

Now I would clear out I_DATA with a series of delete statements: ie.

delete i_data where not werks in s_werks.

etc...

Once again, let me know how you go.

Cheers,

Pat

[Former Member]

very thanx Pat,

i am trying these queries as told by u, then revert back to u as u know they can only give the results in the production sever only may be it takes times, but the information u gave is very helpful to me i think this will solve my problems.

abhishek suppal

[Former Member]

thanx pat

i am able to solve my first problem that is getting data from aufk tables.

abhishek suppal

[former_member221770]

Hi Abhishek,

How did you solve your problem with the AUFK table? I'm always interested in finding new ways of doing things. Any progress on the other queries? Also if you have found any suggestions posted helpful, kindly reward points accordingly.

Cheers,

Pat.

[Former Member]

dear pat,

i m working on the other queries too. i will reward u all the points i don't have any problem in that...rest may be takes time but i can reward u til then....

abhishek suppal