10-02-2008 3:39 PM
Hi all, please, help me with this performance.
The coment lines was the original code.
The previous code got time out and the new code is taking long long time. The range r_mblnr has about 1500 records.
MSEG table has more than 1.500.000 records.
Thanks in advance, regards
RP
** SELECT mblnr mjahr zeile bwart matnr werks lgort charg
** insmk lifnr shkzg waers dmbtr bnbtr bualt menge
** meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
** bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
** smblp
**
** FROM mseg
** INTO TABLE t_mseg_cancel
** FOR ALL ENTRIES IN t_mseg
** WHERE ( smbln EQ t_mseg-mblnr
** AND sjahr EQ t_mseg-mjahr
** AND smblp EQ t_mseg-zeile )
** OR ( lfbnr = t_mseg-mblnr
** AND lfbja = t_mseg-mjahr
** AND lfpos = t_mseg-zeile ).
**
**
** SELECT mblnr mjahr zeile bwart matnr werks lgort charg
** insmk lifnr shkzg waers dmbtr bnbtr bualt menge
** meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
** bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
** smblp
**
** FROM mseg APPENDING TABLE t_mseg_cancel
** FOR ALL ENTRIES IN t_mseg
** WHERE mblnr EQ t_mseg-mblnr
** AND ( lfbnr NE space
** OR smbln NE space ).
LOOP AT t_mseg.
add_range r_mblnr t_mseg-mblnr.
add_range h_mjahr t_mseg-mjahr.
add_range r_zeile t_mseg-zeile.
ENDLOOP.
SORT: r_mblnr BY low,
h_mjahr BY low,
r_zeile BY low.
DELETE ADJACENT DUPLICATES FROM r_mblnr COMPARING low.
DELETE ADJACENT DUPLICATES FROM h_mjahr COMPARING low.
DELETE ADJACENT DUPLICATES FROM r_zeile COMPARING low.
SELECT mblnr mjahr zeile bwart matnr werks lgort charg
insmk lifnr shkzg waers dmbtr bnbtr bualt menge
meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
smblp
FROM mseg
INTO TABLE t_mseg_cancel
FOR ALL ENTRIES IN r_mblnr
WHERE ( smbln = r_mblnr-low
AND sjahr IN r_mjahr
AND smblp IN r_zeile ).
SELECT mblnr mjahr zeile bwart matnr werks lgort charg
insmk lifnr shkzg waers dmbtr bnbtr bualt menge
meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
smblp
FROM mseg
APPENDING TABLE t_mseg_cancel
FOR ALL ENTRIES IN r_mblnr
WHERE ( lfbnr = r_mblnr-low
AND lfbja IN r_mjahr
AND lfpos IN r_zeile ).
SELECT mblnr mjahr zeile bwart matnr werks lgort charg
insmk lifnr shkzg waers dmbtr bnbtr bualt menge
meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
smblp
FROM mseg APPENDING TABLE t_mseg_cancel
FOR ALL ENTRIES IN r_mblnr
WHERE mblnr = r_mblnr-low
AND ( lfbnr NE space
OR smbln NE space ).
10-02-2008 5:52 PM
Hi Rodrigo,
if this statement is really important, I would go back to a single Statement, I would create an additional index and I would change the last statement (may be):
SELECT mblnr mjahr zeile bwart matnr werks lgort charg
insmk lifnr shkzg waers dmbtr bnbtr bualt menge
meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
smblp
FROM mseg
INTO TABLE t_mseg_cancel
FOR ALL ENTRIES IN t_mseg
WHERE ( smbln EQ t_mseg-mblnr
AND sjahr EQ t_mseg-mjahr
AND smblp EQ t_mseg-zeile )
OR ( lfbnr = t_mseg-mblnr
AND lfbja = t_mseg-mjahr
AND lfpos = t_mseg-zeile )
OR ( mblnr EQ t_mseg-mblnr
AND mjahr EQ t_mseg-mjahr " if correct
AND zeile EQ t_mseg-zeile " if correct
AND ( lfbnr NE space OR smbln NE space ) ).
The new secondary Index should contain the fields: LFBNR, LFBJA and LFPOS.
Don't forget to create new statistics after creating the index.
Many people probably disagree with a new secondary index, but to improve your report, it is probably the only solution.
You should not add a new index without good reasons, but a single index does not cost you too much (insert and update performance). You should not tend to create additional indexes for any statement, but on big tables it is sometimes unavoidable.
Best regards
Ralph Ganszky
10-02-2008 3:53 PM
heavy select...
try using package size concept...or execute it in background..
or create new secondary indexex based on your where clause...
10-02-2008 5:52 PM
Hi Rodrigo,
if this statement is really important, I would go back to a single Statement, I would create an additional index and I would change the last statement (may be):
SELECT mblnr mjahr zeile bwart matnr werks lgort charg
insmk lifnr shkzg waers dmbtr bnbtr bualt menge
meins erfmg erfme bpmng bprme ebeln ebelp lfbnr
bukrs lsmng lsmeh lfbja lfpos xwsbr sjahr smbln
smblp
FROM mseg
INTO TABLE t_mseg_cancel
FOR ALL ENTRIES IN t_mseg
WHERE ( smbln EQ t_mseg-mblnr
AND sjahr EQ t_mseg-mjahr
AND smblp EQ t_mseg-zeile )
OR ( lfbnr = t_mseg-mblnr
AND lfbja = t_mseg-mjahr
AND lfpos = t_mseg-zeile )
OR ( mblnr EQ t_mseg-mblnr
AND mjahr EQ t_mseg-mjahr " if correct
AND zeile EQ t_mseg-zeile " if correct
AND ( lfbnr NE space OR smbln NE space ) ).
The new secondary Index should contain the fields: LFBNR, LFBJA and LFPOS.
Don't forget to create new statistics after creating the index.
Many people probably disagree with a new secondary index, but to improve your report, it is probably the only solution.
You should not add a new index without good reasons, but a single index does not cost you too much (insert and update performance). You should not tend to create additional indexes for any statement, but on big tables it is sometimes unavoidable.
Best regards
Ralph Ganszky
10-03-2008 9:57 AM
Hello,
Sometimes, It is less costly to make a loop with select in it.
Instead using a select for all entries or an heavy table join.
Sincerely
Christophe Blineau
10-03-2008 7:02 PM
Thanks for your answers, but it not help in this case.
Already exists one index for smbln sjahr smblp and other to
lfbnr lfbja lfpos.
I tried to change the last (lfbnr lfbja lfpos) including the mandt field, but other parts of the program that do select in MSEG become slow. Nice...
I am listening for other opinion...
continue testing
Regards
10-03-2008 7:39 PM
Once again we see the folly of creating an index to speed up a single SELECT in a single program.
Since part of the WHERE is:
lfbnr NE space
There is no way this SELECT could use the new index.
What I would do is see if there are any secondary index tables that will help or see if the sales document flow table (VBFA) can be used.
If not, run it in the background.
Rob
10-03-2008 7:33 PM
INCREDIBLE
I dont know why, how, what happened.
But when I get back the index without the field MANDT, and transport it to QA, each select run less than 2 seconds...
The index wake up and decide to work!!!
Lol!!!
😛