topic Re: Need help for performance tuning select statement in Application Development and Automation Discussions

Need help for performance tuning select statement

gerd_hotz — Wed, 14 Apr 2010 09:20:11 GMT

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.

ENDSELECT.

BR Gerd

Re: Need help for performance tuning select statement

ThomasZloch — Wed, 14 Apr 2010 09:39:36 GMT

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

http://help.sap.com/abapdocu_70/en/ABENLOGEXP_BOOLE.htm

Re: Need help for performance tuning select statement

Former Member — Wed, 14 Apr 2010 11:51:12 GMT

Use the Standard function modules for extracting Change History.

CHANGEDOCUMENT_READ

CHANGEDOCUMENT_READ_HEADERS

CHANGEDOCUMENT_READ_POSITIONS

Regards

Vinod

Re: Need help for performance tuning select statement

Former Member — Wed, 14 Apr 2010 14:13:04 GMT

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

Re: Need help for performance tuning select statement

Former Member — Wed, 14 Apr 2010 15:53:55 GMT

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

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

Re: Need help for performance tuning select statement

Former Member — Thu, 15 Apr 2010 11:58:34 GMT

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

Re: Need help for performance tuning select statement

gerd_hotz — Thu, 15 Apr 2010 12:52:53 GMT

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

Re: Need help for performance tuning select statement

Former Member — Thu, 15 Apr 2010 13:06:59 GMT

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

Re: Need help for performance tuning select statement

Former Member — Thu, 15 Apr 2010 16:07:42 GMT

> 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.

Re: Need help for performance tuning select statement

ThomasZloch — Thu, 15 Apr 2010 16:21:12 GMT

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

Re: Need help for performance tuning select statement

gerd_hotz — Fri, 16 Apr 2010 08:55:12 GMT

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 !!!