topic Re: Query optimizaing for performance tune in Application Development and Automation Discussions

Query optimizaing for performance tune

Former Member — Mon, 19 Jul 2010 05:25:34 GMT

Hi guys,

The below query is taking a lot of time to execute.The main problem is with table S022.This table is going for full table scan everytime even if we give a lot of selection criteria.So is there any alternate table for S022.In selection screen the mandatory fields are Location,plant and Phase.

SELECT a~aufnr "Order number

e~arbpl "Work center

e~plnum "Plaaned order

b~objnr "Object number

a~werks "plant

c~charg "Batch

d~gamng "qty

d~gmein "UOM

d~plnbez "Material

d~aufpl "routing no

d~aprio "Order priority

d~fevor "Responsible planner group/department

f~aufpl "Routing no

f~aplzl "general counter

f~vornr "Phase

f~arbid "workcenter

f~ltxa1 "text

g~objty "Object type

g~stand " Location

INTO CORRESPONDING FIELDS OF TABLE t_afpo

FROM ( ( ( ( ( s022 AS e

INNER JOIN aufk AS a ON a_{aufnr = e}aufnr )

INNER JOIN jest AS b ON a_{objnr = b}objnr )

INNER JOIN afpo AS c ON c_{aufnr = a}aufnr

AND a_{werks = c}dwerk )

INNER JOIN afko AS d ON d_{aufnr = c}aufnr

INNER JOIN afvc AS f ON f_{aufpl = d}aufpl

AND f_{vornr = e}vornr )

INNER JOIN crhd AS g ON g_{arbpl = e}arbpl

AND g_{objid = f}arbid )

WHERE a~werks IN s_werks

AND d~plnbez IN s_matnr

AND d~fevor IN s_fevor

AND e~arbpl IN s_arbpl

AND e~aufnr IN s_aufnr

AND a~auart IN r_auart

AND e~ssavd IN s_date

AND e~vornr IN s_vornr

AND g~stand IN s_stand

AND a~loekz NE 'X'

AND b~stat = c_i0002

AND b~inact = space.

Re: Query optimizaing for performance tune

Former Member — Mon, 19 Jul 2010 06:09:50 GMT

Hi,

i would suggest that dont put too many joins in the select statement..

if possible try to put mulitple select statements using "for all entries".

and also join the table only with key fields so that the data fetching will be more faster..

if you are not able to use the key fields try with index fields provided by SAP..

if that is also not working then create Zindex field for the table which is using more time to fetch the data.

Regards

Re: Query optimizaing for performance tune

Former Member — Mon, 19 Jul 2010 06:24:20 GMT

Hi JK,

You mean to say Zindex for the SAP standard tables?..Please elaborate in detail.

Re: Query optimizaing for performance tune

Former Member — Thu, 22 Jul 2010 07:23:45 GMT

Hi,

Sorry for the late response. i was on vacation.

yes.. we can create Zindex in the standard table which will act as an secondary index and will reterive the data faster.

Regards

Re: Query optimizaing for performance tune

Former Member — Tue, 27 Jul 2010 11:28:10 GMT

JK, could you please tell me the procedure for that..

Re: Query optimizaing for performance tune

Former Member — Wed, 22 Dec 2010 06:18:30 GMT

Solved..