04-14-2010 10:20 AM
Hello experts,
can anybody check the coding how to speed up the performance ?
The bottleneck seems to ne in the selection from cdpos and cdhdr,
but this are large tables.
SELECT * FROM cdhdr WHERE objectclas = 'DEBI' AND tcode = 'VD02' AND udate = sy-datum
OR objectclas = 'DEBI' AND tcode = 'XD02' AND udate = sy-datum.
IF sy-subrc = 0.
changenr = cdhdr-changenr.
objectid = cdhdr-objectid.
SELECT SINGLE * FROM cdpos INTO wa_cdpos WHERE changenr = changenr AND fname = 'AUFSD'
OR changenr = changenr AND fname = 'LIFSD'
OR changenr = changenr AND fname = 'FAKSD'.
IF wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '01' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '02' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '03' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'SL'.
APPEND wa_cdpos TO it_cdpos.
ENDIF.
IF sy-subrc = 0.
objectid = cdpos-objectid.
fname = cdpos-fname.
chngind = cdpos-chngind.
value_new = cdpos-value_new.
value_old = cdpos-value_old.
kundennummer = cdpos-objectid.
PERFORM email.
ENDIF.
ENDIF.
ENDSELECT.
BR Gerd
04-14-2010 4:53 PM
if there are standard interfaces then try to use them.
You should always try to write a complete WHERE-clause, and of course try to be simple
IF wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '01' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '02' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '03' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'SL'.
is the same as
AND cdpos-chngind = 'U'
AND cdpos-value_new IN ( '01' , '02', '03', 'VK', 'SL' )
Note, that lines 2 and 5 are identical !!
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
Try to convert the SELECT - SELECT SINGLE - ENDSELECT into a Join!
Ich hätte auch eine Literaturempfehlung in Deutsch:
http://www.dpunkt.de/buecher/3096.html
Siegfried
04-14-2010 10:39 AM
Be careful with the AND and OR conditions without using brackets. I suggest you surround the AND conditions with brackets to be on the safe side and avoid unexpected results caused by "implicit bracketing".
Thomas
04-14-2010 12:51 PM
Use the Standard function modules for extracting Change History.
CHANGEDOCUMENT_READ
CHANGEDOCUMENT_READ_HEADERS
CHANGEDOCUMENT_READ_POSITIONS
Regards
Vinod
04-14-2010 3:13 PM
Hi Gerd,
The select on CDPOS looks very fishy to me. Your WHERE clause only has CHANGENR and FNAME, and since CDPOS is a cluster table (i.e. no indexes apart from the primary), this will trigger a full serial scan.
You might try this one, specifying at least the full primary key from the CDHDR record:
SELECT * FROM cdhdr
WHERE objectclas = 'DEBI'
AND udate = sy-datum
AND ( tcode = 'VD02' or tcode = 'XD02' ).
IF sy-subrc = 0.
SELECT SINGLE * FROM cdpos INTO wa_cdpos WHERE
objectclas = cdhdr-objectclas AND
objectid = cdhdr-objectid AND
changenr = cdhdr-changenr AND
( fname = 'AUFSD' OR fname = 'LIFSD' or fname = 'FAKSD' ).
The code you comes from inside a SELECT loop. It might be better to collect the CDHDR records in an internal table and then use FOR ALL ENTRIES in the CDPOS select (or use a join).
Regards,
Mark
04-14-2010 4:53 PM
if there are standard interfaces then try to use them.
You should always try to write a complete WHERE-clause, and of course try to be simple
IF wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '01' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '02' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = '03' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'SL'.
is the same as
AND cdpos-chngind = 'U'
AND cdpos-value_new IN ( '01' , '02', '03', 'VK', 'SL' )
Note, that lines 2 and 5 are identical !!
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
wa_cdpos-chngind = 'U' AND wa_cdpos-value_new = 'VK' OR
Try to convert the SELECT - SELECT SINGLE - ENDSELECT into a Join!
Ich hätte auch eine Literaturempfehlung in Deutsch:
http://www.dpunkt.de/buecher/3096.html
Siegfried
04-15-2010 12:58 PM
Hello Gerd,
My preference when accessing change history (or any other set of very large tables) is to try to make my select queries using the key fields and then process the output seperately.
CDHDR contains a lot of entries and trying to access the TCODE and UDATE fields within the select query is slow. Similarly, the CDPOS table is also large and slow to search on fields other than the key ones. Instead try the following.
data:
lt_cdhdr type standard table of cdhdr with header line.
field-symbols:
<cdhdr> type cdhdr.
* get relevant CDHDR entries
select *
from cdhdr
into lt_cdhdr
where OBJECTCLAS = 'DEBI'.
* clean up extracted header lines
delete lt_cdhdr where udate <> sy-datum.
delete it_cdhdr where tcode <> 'VD02' and tcode <> 'XD02'.
this leaves justthe header lines you want.
* loop through headers and get associated details.
loop at lt_cdhdr assigning <cdhdr>.
select *
from cdpos
where objectclas = <cdhdr>-objectclas
and objectid = <cdhdr>-objectid
and changenr = <cdhdr>-changenr
and fname in ('AUFSD','LIFSD','FAKSD').
if cdpos-chngind = 'U' and cdpos-value_new in ( '01','02','03','SL','VK' ).
PERFORM REQUIRED TASKS FOR SENDING EMAIL.
* you wanted to select a single CDPOS per change header so now end this loop pass.
continue.
endif.
endselect.
endloop.
I haven't tested this particular section of code but it should work a lot quicker than what you had. I applied the same approach to a change history report on purchase orders and it ran a lot quicker than any other code I found on the web.
regards,
John
04-15-2010 1:52 PM
Hi,
the line
if cdpos-chngind = 'U' and cdpos-value_new in ( '01','02','03','SL','VK' ).
always returns error: Comma without preceding colon (after IF ?).
Has anaybody a working code ?
Thanks Gerd
04-15-2010 2:06 PM
Hi Gerd,
the following will work:
if cdpos-chngind = 'U'
and ( cdpos-value_new = '01'
or cdpos-value_new = '02'
or cdpos-value_new = '03'
or cdpos-value_new = 'SL'
or cdpos-value_new = '01' ).
The "IN" statement does work in select queries and I thought it also worked in "IF" statements but without testing in a program I can't say for certain.
Regards,
John
04-15-2010 5:07 PM
> if cdpos-chngind = 'U' and cdpos-value_new in ( '01','02','03','SL','VK' ).
this is no ABAP, I wrote AND and it should be added to the WHERE-condition.
04-15-2010 5:21 PM
my summary so far:
1) use full primary key when selecting from CDPOS
2) make sure the WHERE-conditions are properly structured and work as expected
3) read Siegfried's book
Please let us know how you are doing.
Thomas
04-16-2010 9:55 AM
Hello again,
I've updated my coding:
SELECT * FROM cdhdr WHERE objectclas = 'DEBI' AND
tcode IN ('VD02' , 'XD02' , 'VD03' , 'XD03') AND
udate = sy-datum.
IF sy-subrc = 0.
changenr = cdhdr-changenr.
objectid = cdhdr-objectid.
username = cdhdr-username.
SELECT SINGLE * FROM cdpos INTO wa_cdpos WHERE changenr = changenr AND
tabname = 'KNA1' AND
objectclas = 'DEBI' AND
chngind = 'U' AND
fname IN ('AUFSD' , 'LIFSD' , 'FAKSD' ) AND
value_new IN ('01' , 'VK' , '02' , '03' , 'SL' ).
....
....
and now its running fast !
Maybe with a join - selection it could be better, but its okay now.
Thanks for help !!!