Problem with Select in Production

[Former Member]

hi all,

My Select query is working fine in Devlopment server, but it is going to Dump in Production. In ST05, it is showing time-out error, may be due to heavy data, i guess. Is there any way to modify this query sothat it wont go to Dump in Production. Plz have a look:

select a~budat as sdate

a~vkont as contAcc

a~xblnr as contract

b~hkont as subTrans

b~betrw as amount

c~ktext as product

d~vkbez as customer

e~zzdepot as depot

into corresponding fields of table iData

from dfkkop as a

inner join dfkkopk as b

on a_{opbel = b}opbel

and a_{opupk = b}opupk

left join cskt as c

on c_{kostl = b}kostl

inner join fkkvkp as d

on d_{vkont = a}vkont

inner join but000 as e

on e_{partner = d}gpart

where

a~vkont in rAcc

and a~budat in rDate

and a~hvorg = 'CMS'

and a~bukrs in sobukrs

and a~tvorg in rComp

and a~opupz = 000

and zzdepot in rDepot.

• rDate, rComp, rDepot, sobukrs are select-options in ****selection screen

Thanks in advance,

Regards,

HKM

[Former Member]

Hi,

remove inner join and use all entries and follow below steps

1) Remove corresponding from select satement

2) Remove * from select

3) Select field in sequence as defined in database

4) Avoid unnecessary selects

i.e check for internal table not initial

5) Use all entries and sort table by key fields

6) Remove selects ferom loop and use binary search

7) Try to use secondary index when you don't have

full key.

😎 Modify internal table use transporting option

9) Avoid nested loop . Use read table and loop at itab

from sy-tabix statement.

10) free intrenal table memory when table is not

required for further processing.

11)

Follow below logic.

FORM SUB_SELECTION_AUFKTAB.

if not it_plant[] is initial.

it_plant1[] = it_plant[].

sort it_plant1 by werks.

delete adjacent duplicates from it_plant1 comparing werks

SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

FROM AUFK

FOR ALL ENTRIES IN it_plant1

WHERE AUFNR IN S_AUFNR AND

KTEXT IN S_KTEXT AND

• WERKS IN S_WERKS AND

AUART IN S_AUART AND

USER4 IN S_USER4 AND

werks eq it_plant1-werks.

free it_plant1.

Endif.

ENDFORM. "SUB_SELECTION_AUFKTAB

Regards

amole

[Former Member]

Hi

If possible try to reduce the joins.

May be you can split the query into multiple selects and then you can insert the data into table iData by using read table statements.

Just try using this may be it will work.

Thanks & Regards

Deepti

[andrea_galluccio2]

Hi,

just a suggestion.

Probably u don't fill any select-option.

Try to fill just one of them.

If this doesen't help, try to watch the estimated cost of the query to see if the right indexes are used.

U can see the estimated cost from st04 -> Sql requests -> check the pid of the workprocess used by the query

Hope it helps

Bye

Andrea

[anversha_s]

hi vinny,

some sugestions.

1. avoid join and use FOR ALL ENTRIES.

2.never write SELECT query inside the loop.

3.never use INTO CORRESSPONDING FIELD OF TABLE, use INTO TABLE.

4.use READ TABLE WITH BINARY SEARCH, after SORTING.

5.Inside the condition, if the condition is satisfied, use EXIT.

rgds

anver

if helped mark points

[Former Member]

HI,

in ur code u r accessing 5 db tables and going with innerjoins.

so first select data from table and put in each itab.

based on the data in itab1 ,go with 'for all entries'

this will dont give u dump.

[Former Member]

I would advice to split the SELECT, thereby reducing the number of joins.

select a~budat as sdate

a~vkont as contAcc

a~xblnr as contract

b~hkont as subTrans

b~betrw as amount

b~kostl

a~vkont

into corresponding fields of table iData

from dfkkop as a

inner join dfkkopk as b

on a_{opbel = b}opbel

and a_{opupk = b}opupk

where

a~vkont in rAcc

and a~budat in rDate

and a~hvorg = 'CMS'

and a~bukrs in sobukrs

and a~tvorg in rComp

and a~opupz = 000.

ivkont[] = idata[].

ikostl[] = idata[].

sort ivkont by vkont.

delete adjacent duplicates in ivkont.

sort ikostl by kostl.

delete adjacent duplicates in ikostl.

select data from cskt using for all entries in ikostl.

select data from fkkvkp and but000 using for all entries in ivkont.

Loop at idata.

read icsktdata.

read ifkkvkpdata.

modify idata.

endloop.