performace problem in a select statment how to imporve the performance

[Former Member]

fist select statment

SELECT a~extno

a~guid_lclic " for next select

e~ctsim

e~ctsex

*revised spec 3rd

f~guid_pobj

f~amnt_flt

f~amcur

f~guid_mobj

e~srvll "pk

e~ctsty "PK

e~lgreg "PK

INTO TABLE gt_sagmeld

FROM /SAPSLL/LCLIC as a

INNER JOIN /sapsll/tlegsv as e on e_{lgreg = a}lgreg

• * revised spec 3rd

inner join /sapsll/legcon as f on f_{guid_lclic = a}guid_lclic " for ccngn1 selection

inner join /sapsll/corcts as g on g_{guid_pobj = f}guid_pobj

where a~extno in s_extno.

sort gt_sagmeld by guid_lclic guid_pobj.

• lgreg ctsty srvll

delete adjacent duplicates from gt_sagmeld comparing guid_lclic guid_pobj.

it selects about 20 lakh records

belos select statment whichs is taking time as it is based on the entreis of gt_sagmeld

select /sapsll/corpar~guid_mobj

/sapsll/corpar~PAFCT

but000~bpext

but000~partner

/sapsll/corpar~parno

into table gt_but001

from /sapsll/corpar

INNER join but000 on but000_{partner = /sapsll/corpar}parno

for all entries in gt_sagmeld

where /sapsll/corpar~guid_mobj = gt_sagmeld-guid_mobj

and /sapsll/corpar~PAFCT = 'SH'.

SELECT /sapsll/cuit~guid_cuit " PK

/sapsll/cuit~QUANT_FLT " to be displayed

/sapsll/cuit~QUAUM " to be displayed

/sapsll/cuit~RPTDT " to be displayed

/sapsll/cuhd~guid_cuhd " next select

/sapsll/cuit~guid_pr " next select

INTO table gt_sapsllcuit

FROM /sapsll/cuit

inner join /sapsll/cuhd on /sapsll/cuit_{guid_cuhd = /sapsll/cuhd}guid_cuhd

FOR all entries in gt_sagmeld

WHERE /sapsll/cuit~guid_cuit = gt_sagmeld-guid_pobj.

Delete adjacent duplicates from gt_sapsllcuit[].

if not gt_sapsllcuit[] is initial.

[Former Member · Accepted solution]

Hi

You need to optimize the first select too but let us look at the second and third select statments first.

The internal table gt_sagmeld contains 20 lakh records as mentioned.

data: gt_sagmeld_temp like table of gt_sagmeld.

gt_sagmeld_temp[] = gt_sagmeld[].

sort gt_sagmeld_temp by guid_mobj.

delete adjacent duplicates from gt_sagmeld_temp comparing guid_mobj.

**If there are 5000 distinct guid_mobj or maybe more but certainly not 20 lakhs, then the temp table will contain less number of records. So, now you can use this temp table for all entries.

select /sapsll/corpar~guid_mobj

/sapsll/corpar~PAFCT

but000~bpext

but000~partner

/sapsll/corpar~parno

into table gt_but001

from /sapsll/corpar

INNER join but000 on but000_{partner = /sapsll/corpar}parno

for all entries in gt_sagmeld_temp

where /sapsll/corpar~guid_mobj = gt_sagmeld_temp-guid_mobj

and /sapsll/corpar~PAFCT = 'SH'.

**Similarly

gt_sagmeld_temp[] = gt_sagmeld[].

sort gt_sagmeld_temp by guid_pobj.

delete adjacent duplicates from gt_sagmeld_temp comparing guid_pobj.

SELECT /sapsll/cuit~guid_cuit " PK

/sapsll/cuit~QUANT_FLT " to be displayed

/sapsll/cuit~QUAUM " to be displayed

/sapsll/cuit~RPTDT " to be displayed

/sapsll/cuhd~guid_cuhd " next select

/sapsll/cuit~guid_pr " next select

INTO table gt_sapsllcuit

FROM /sapsll/cuit

inner join /sapsll/cuhd on /sapsll/cuit_{guid_cuhd = /sapsll/cuhd}guid_cuhd

FOR all entries in gt_sagmeld_temp

WHERE /sapsll/cuit~guid_cuit = gt_sagmeld_temp-guid_pobj.

Delete adjacent duplicates from gt_sapsllcuit[].

Regards

Navneet

[Former Member · Accepted solution]

hi navneet

i tried u code

firstly still data is huge if i sort gt_sagmeld 1205873x232 so many records are there

secondly the statement gt_sagmeld_temp[] = gt_sagmeld[] is givign error

gt_sagmeld_temd cannto be converted to type gt_sagmeld

AND TH IS IS MY FIRST SELECT STATEMENT BUT IS NOT GIVIING ANY PROBLEM IT IS EXECUTING FAST ONLY PROBLEM IS THEREAFTER

SELECT a~extno

a~guid_lclic " for next select

e~ctsim

e~ctsex

*revised spec 3rd

f~guid_pobj

f~amnt_flt

f~amcur

f~guid_mobj

e~srvll "pk

e~ctsty "PK

e~lgreg "PK

INTO TABLE gt_sagmeld

FROM /SAPSLL/LCLIC as a

INNER JOIN /sapsll/tlegsv as e on e_{lgreg = a}lgreg

• * revised spec 3rd

inner join /sapsll/legcon as f on f_{guid_lclic = a}guid_lclic " for ccngn1 selection

inner join /sapsll/corcts as g on g_{guid_pobj = f}guid_pobj

where a~extno in s_extno.

• and e~srvll = 'LECLIC'

• and e~ctsty = '002'.

sort gt_sagmeld by guid_lclic lgreg ctsty srvll guid_mobj guid_pobj.

delete adjacent duplicates from gt_sagmeld comparing guid_lclic lgreg ctsty srvll guid_mobj guid_pobj.

REGARDS

ARORA

reagards

Arora

[Former Member · Accepted solution]

hi all

i ahve broken the statements but getting stuck at last select

SELECT a~extno

a~guid_lclic " for next select but000

e~ctsim

e~ctsex

e~srvll

e~ctsty

e~lgreg

INTO TABLE gt_sagmeld

FROM /SAPSLL/LCLIC as a

INNER JOIN /sapsll/tlegsv as e on e_{lgreg = a}lgreg

where a~extno in s_extno.

sort gt_sagmeld by guid_lclic.

delete adjacent duplicates from gt_sagmeld comparing all fields.

IF not gt_sagmeld[] is initial.

SELECT /sapsll/legcon~guid_lclic

/sapsll/legcon~guid_pobj

/sapsll/legcon~amnt_flt

/sapsll/legcon~amcur

/sapsll/legcon~guid_mobj

INTO TABLE gt_but000

FROM /SAPSLL/LEGCON

for all entries in gt_sagmeld

where /SAPSLL/legcon~guid_lclic = gt_sagmeld-guid_lclic.

and /sapsll/corpar~PAFCT = 'SH' .

IF NOT GT_BUT000[] IS INITIAL.

sort gt_but000 by guid_mobj.

delete adjacent duplicates from gt_but000 comparing guid_mobj.

select /sapsll/corpar~guid_mobj

/sapsll/corpar~parno

into table gt_but001

from /sapsll/corpar

for all entries in gt_but000

where /sapsll/corpar~guid_mobj = gt_but000-guid_mobj

and /sapsll/corpar~PAFCT = 'SH'.

PROBLEM IS THAT NOW THE TABLE GT_BUT00 HAS 14000 RECORDS UNIQUE OF GUID_POBJ

SO THIS ABOVE LAST STAMENT IS NOT EXECUTING AND HANGING UP/

pLEASE SUGGGEST

[Former Member · Accepted solution]

Hi

>gt_sagmeld_temd cannto be converted to type gt_sagmeld

You have got this error becuase the temp table is not same as gt_sagmeld.

Declare a temporary table of the same type as gt_sagmeld and then copy the records in the temp table.

Then try the logic i had sent.

Regards

Navneet

[Former Member · Accepted solution]

Hi

>select /sapsll/corpar~guid_mobj

>/sapsll/corpar~parno

>into table gt_but001

>from /sapsll/corpar

>for all entries in gt_but000

>where /sapsll/corpar~guid_mobj = gt_but000-guid_mobj

>and /sapsll/corpar~PAFCT = 'SH'.

Remove the condition for PAFCT from the where condition. Add PAFCT in the internal table gt_but001 and populate this field too.

select /sapsll/corpar~guid_mobj

/sapsll/corpar~parno

/sapsll/corpar~PAFCT

into table gt_but001

from /sapsll/corpar

for all entries in gt_but000

where /sapsll/corpar~guid_mobj = gt_but000-guid_mobj.

DELETE gt_but001 where PAFCT <> 'SH'.

If this doesn't work out, then there is an alternative way too.

You will have to build a range table for guid_mobj and then use the range table in parts to populate the internal table.

suppose you have populated the range table r_guid_mobj.

Declare another range table for guid_mobj, say r_temp.

index_start = 1.

index_end = c_records. "give a suitable value, say 500

DESCRIBE TABLE r_guid_mobj lines l_line.

Do.

append lines of r_guid_mobj into r_temp from index_start to index_end.

Select...

if w_exit = true.

exit.

endif.

Index_start = index_end + 1.

index_add = l_line - c_records.

if index_add gt c_records.

index_end = index_end + c_records.

else.

index_end = index_end + index_add.

w_exit = true.

endif.

ENDDO.

Regards

Navneet

[Former Member · Accepted solution]

hi navenet

that didnt worked

we need to try ur range options

but not sure what you told in the last mail as not clear with range can u pls eloboragte more i am pasting the full code here

SELECT a~extno

a~guid_lclic " for next select but000

e~ctsim

e~ctsex

e~srvll

e~ctsty

e~lgreg

INTO TABLE gt_sagmeld

FROM /SAPSLL/LCLIC as a

INNER JOIN /sapsll/tlegsv as e on e_{lgreg = a}lgreg

where a~extno in s_extno.

sort gt_sagmeld by guid_lclic.

delete adjacent duplicates from gt_sagmeld comparing all fields.

IF not gt_sagmeld[] is initial.

SELECT /sapsll/legcon~guid_lclic

/sapsll/legcon~guid_pobj

/sapsll/legcon~amnt_flt

/sapsll/legcon~amcur

• but000~bpext

• *revised spec

• /sapsll/corpar~PAFCT

/sapsll/legcon~guid_mobj

• /sapsll/cuit~guid_cuit

INTO TABLE gt_but000

FROM /SAPSLL/LEGCON

for all entries in gt_sagmeld

where /SAPSLL/legcon~guid_lclic = gt_sagmeld-guid_lclic.

IF NOT GT_BUT000[] IS INITIAL.

sort gt_but000 by guid_mobj.

delete adjacent duplicates from gt_but000 comparing guid_mobj.

select /sapsll/corpar~guid_mobj

/sapsll/corpar~PAFCT

/sapsll/corpar~parno

into table gt_but001

from /sapsll/corpar

for all entries in gt_but000

where /sapsll/corpar~guid_mobj = gt_but000-guid_mobj.

• and /sapsll/corpar~PAFCT = 'SH'.

DELETE gt_but001 where PAFCT <> 'SH'.

*sort gt_corpar by parno.

*delete adjacent duplicates from gt_corpar comparing parno.

*

*select gd000~partner

• gd000~bpext

*

• from gd000 into table gt_but001

*

• for all entries in gt_corpar

• where gd000~partner = gt_corpar-parno.

my ultimat aim is to select bpext from gd000

can u please explain how to use ranges here and what is the singnificance and how ill i read the data from the final table if we use ranges

regards

Nishant

[Former Member · Accepted solution]

Hi

Since the number of entries in the internal table is large, the select is taking lot of time resulting in time out error.

So, you do multiple selects in blocks. You will be appending internal table for a small ranges of guid_mobj instead of entire range while using for all entries.

I will send you a example code in sometime which you can relate and work out.

Regards

Navneet

[KjetilKilhavn]

I am not familiar with the specific tables (/SAPSLL/LCLIC does not exist in the system here), and in any case the specifics will always be client-dependent, but some general advice:

You say you have around 2 million entries from your first selection. How many entries do you have after you have completed all the selections? If you have few it would perhaps be better to turn the order of selections around. In some cases it is better, as you can then get a small(er) set from which you perform the reduction.

Another option which can be useful in case the high memory use is causing bad performance is to perform the selection in chunks using a cursor. More code, but memory use can be reduced significantly and will not vary as much.

<i>PS: If you use the <b>Code</b> button your code sample will be <u>much</u> easier to read.</i>

Message was edited by Kjetil Kilhavn:

Just to make myself a little clearer. You would initially get a bigger set than your final set by selecting first from /SAPSLL/CUIT. However, by looping at this set and checking if each entry exists in GT_SAGMELD, or even by selecting from the tables with FOR ALL ENTRIES IN GT_SAPLLCUIT you can possibly improve the overall performance, and even reduce the memory use.

[Former Member]

hi All

conside the code below and the number of entries AND FIND COMMENT AGAINST EACH SELECT FIRST SELECTS IS OK AND

SELECT a~extno

a~guid_lclic " for next select but000

e~ctsim

e~ctsex

e~srvll

e~ctsty

e~lgreg

INTO TABLE gt_sagmeld

FROM /SAPSLL/LCLIC as a

INNER JOIN /sapsll/tlegsv as e on e~lgreg = a~lgreg

where a~extno in s_extno.

<b>THIS IS OK AS THE NUMBER OF RECORDS IS Table[43x176] AND NOT ROBLEM IN EXECUTION FAST</b>

sort gt_sagmeld by guid_lclic.

delete adjacent duplicates from gt_sagmeld comparing all fields.

IF not gt_sagmeld[] is initial.

SELECT /sapsll/legcon~guid_lclic

/sapsll/legcon~guid_pobj

/sapsll/legcon~amnt_flt

/sapsll/legcon~amcur

/sapsll/legcon~guid_mobj

INTO TABLE gt_but000

FROM /SAPSLL/LEGCON

for all entries in gt_sagmeld

where /SAPSLL/legcon~guid_lclic = gt_sagmeld-guid_lclic.

<b>THIS ABOVE IS ALSO OK THOGH NOW THE RECORDS ARE MUCH MORE

Table[1022259x72]</b>

IF NOT GT_BUT000[] IS INITIAL.

sort gt_but000 by guid_pobj.

delete adjacent duplicates from gt_but000 comparing guid_mobj.

*

select /sapsll/corpar~guid_mobj

/sapsll/corpar~PAFCT

/sapsll/corpar~parno

into table gt_BUT001

from /sapsll/corpar

for all entries in gt_but000

where /sapsll/corpar~guid_mobj = gt_but000-guid_mobj

and /sapsll/corpar~PAFCT = 'SH'.

<b>*DELETE gt_but001 where PAFCT <> 'SH'. I TIRED TO REMOVE THE CONDITON FOR WHERE ALSO BUT THIS ABOVE STATEMENT IS HANGING UP</b>

AND SO THE THE BELOW ALSO?

sort gt_BUT001 by parno.

delete adjacent duplicates from gt_BUT001 comparing parno.

select but000~partner

but000~bpext

from but000 into table gt_but002

for all entries in gt_BUT002

where but000~partner = gt_corpar-parno.

REAGARDS

Arora

[Former Member]

hi,

in this case u can avoid inner join instead u can use for all entries in.

reward points if useful,

regards,

seshu.