topic Re: Database in Application Development and Automation Discussions

Database

Former Member — Fri, 03 Aug 2007 10:02:31 GMT

Hi experts

When i run my report , the result of Runtime analysis is.

ABAP - 121,555 = 1.2%

DATABASE - 10,071,967 = 98.6%

SYSTEM - 18,115 = 0.2%

How to increase the DATABASE performance and

how to make the report faster.

Thanks in advance.

Regards

Rajaram

Re: Database

Former Member — Fri, 03 Aug 2007 10:04:33 GMT

Hi Raja

is it possible to shareyour code in forum then only we will tell why it is taking that much amount of time

Best regds,

kiran

Re: Database

Former Member — Fri, 03 Aug 2007 10:05:35 GMT

Hi,

1. For all entries

2. Nested selects

3. Select using JOINS

4. Use the selection criteria

5. Use the aggregated functions

6. Select with view

7. Select with index support

8. Select … Into table

9. Select with selection list

10. Key access to multiple lines

11. Copying internal tables

12. Modifying a set of lines

13. Deleting a sequence of lines

14. Linear search vs. binary

15. Comparison of internal tables

16. Modify selected components

17. Appending two internal tables

18. Deleting a set of lines

19. Tools available in SAP to pin-point a performance problem

20. Optimizing the load of the database

For all entries

The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.

The plus

• Large amount of data

• Mixing processing and reading of data

• Fast internal reprocessing of data

• Fast

The Minus

• Difficult to program/understand

• Memory could be critical (use FREE or PACKAGE size)

Some steps that might make FOR ALL ENTRIES more efficient:

• Removing duplicates from the driver table

• Sorting the driver table

• If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:

FOR ALL ENTRIES IN i_tab

WHERE mykey >= i_tab-low and

mykey <= i_tab-high.

Nested selects

The plus:

• Small amount of data

• Mixing processing and reading of data

• Easy to code - and understand

The minus:

• Large amount of data

• when mixed processing isn’t needed

• Performance killer no. 1

Select using JOINS

The plus

• Very large amount of data

• Similar to Nested selects - when the accesses are planned by the programmer

• In some cases the fastest

• Not so memory critical

The minus

• Very difficult to program/understand

• Mixing processing and reading of data not possible

Use the selection criteria

SELECT * FROM SBOOK.

CHECK: SBOOK-CARRID = 'LH' AND

SBOOK-CONNID = '0400'.

ENDSELECT.

SELECT * FROM SBOOK

WHERE CARRID = 'LH' AND

CONNID = '0400'.

ENDSELECT.

Use the aggregated functions

C4A = '000'.

SELECT * FROM T100

WHERE SPRSL = 'D' AND

ARBGB = '00'.

CHECK: T100-MSGNR > C4A.

C4A = T100-MSGNR.

ENDSELECT.

SELECT MAX( MSGNR ) FROM T100 INTO C4A

WHERE SPRSL = 'D' AND

ARBGB = '00'.

Select with view

SELECT * FROM DD01L

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

SELECT SINGLE * FROM DD01T

WHERE DOMNAME = DD01L-DOMNAME

AND AS4LOCAL = 'A'

AND AS4VERS = DD01L-AS4VERS

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

SELECT * FROM DD01V

WHERE DOMNAME LIKE 'CHAR%'

AND DDLANGUAGE = SY-LANGU.

ENDSELECT.

Select with index support

SELECT * FROM T100

WHERE ARBGB = '00'

AND MSGNR = '999'.

ENDSELECT.

SELECT * FROM T002.

SELECT * FROM T100

WHERE SPRSL = T002-SPRAS

AND ARBGB = '00'

AND MSGNR = '999'.

ENDSELECT.

Select … Into table

REFRESH X006.

SELECT * FROM T006 INTO X006.

APPEND X006.

ENDSELECT

SELECT * FROM T006 INTO TABLE X006.

Select with selection list

SELECT * FROM DD01L

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

ENDSELECT

SELECT DOMNAME FROM DD01L

INTO DD01L-DOMNAME

WHERE DOMNAME LIKE 'CHAR%'

AND AS4LOCAL = 'A'.

ENDSELECT

Key access to multiple lines

LOOP AT TAB.

CHECK TAB-K = KVAL.

" ...

ENDLOOP.

LOOP AT TAB WHERE K = KVAL.

" ...

ENDLOOP.

Copying internal tables

REFRESH TAB_DEST.

LOOP AT TAB_SRC INTO TAB_DEST.

APPEND TAB_DEST.

ENDLOOP.

TAB_DEST[] = TAB_SRC[].

Modifying a set of lines

LOOP AT TAB.

IF TAB-FLAG IS INITIAL.

TAB-FLAG = 'X'.

ENDIF.

MODIFY TAB.

ENDLOOP.

TAB-FLAG = 'X'.

MODIFY TAB TRANSPORTING FLAG

WHERE FLAG IS INITIAL.

Deleting a sequence of lines

DO 101 TIMES.

DELETE TAB_DEST INDEX 450.

ENDDO.

DELETE TAB_DEST FROM 450 TO 550.

Linear search vs. binary

READ TABLE TAB WITH KEY K = 'X'.

READ TABLE TAB WITH KEY K = 'X' BINARY SEARCH.

Comparison of internal tables

DESCRIBE TABLE: TAB1 LINES L1,

TAB2 LINES L2.

IF L1 <> L2.

TAB_DIFFERENT = 'X'.

ELSE.

TAB_DIFFERENT = SPACE.

LOOP

AT TAB1.

READ TABLE TAB2 INDEX SY-TABIX.

IF TAB1 <> TAB2.

TAB_DIFFERENT = 'X'. EXIT.

ENDIF.

ENDLOOP.

ENDIF.

IF TAB_DIFFERENT = SPACE.

" ...

ENDIF.

IF TAB1[] = TAB2[].

" ...

ENDIF.

Modify selected components

LOOP AT TAB.

TAB-DATE = SY-DATUM.

MODIFY TAB.

ENDLOOP.

WA-DATE = SY-DATUM.

LOOP AT TAB.

MODIFY TAB FROM WA TRANSPORTING DATE.

ENDLOOP.

Appending two internal tables

LOOP AT TAB_SRC.

APPEND TAB_SRC TO TAB_DEST.

ENDLOOP

APPEND LINES OF TAB_SRC TO TAB_DEST.

Deleting a set of lines

LOOP AT TAB_DEST WHERE K = KVAL.

DELETE TAB_DEST.

ENDLOOP

DELETE TAB_DEST WHERE K = KVAL.

Tools available in SAP to pin-point a performance problem

• The runtime analysis (SE30)

• SQL Trace (ST05)

• Tips and Tricks tool

• The performance database

Optimizing the load of the database

Using table buffering

Using buffered tables improves the performance considerably. Note that in some cases a statement can not be used with a buffered table, so when using these statements the buffer will be bypassed. These statements are:

• Select DISTINCT

• ORDER BY / GROUP BY / HAVING clause

• Any WHERE clause that contains a sub query or IS NULL expression

• JOIN s

• A SELECT... FOR UPDATE

If you wan t to explicitly bypass the buffer, use the BYPASS BUFFER addition to the SELECT clause.

Use the ABAP SORT Clause Instead of ORDER BY

The ORDER BY clause is executed on the database server while the ABAP SORT statement is executed on the application server. The database server will usually be the bottleneck, so sometimes it is better to move the sort from the database server to the application server.

If you are not sorting by the primary key ( E.g. using the ORDER BY PRIMARY key statement) but are sorting by another key, it could be better to use the ABAP SORT statement to sort the data in an internal table. Note however that for very large result sets it might not be a feasible solution and you would want to let the database server sort it.

Avoid the SELECT DISTINCT Statement

As with the ORDER BY clause it could be better to avoid using SELECT DISTINCT, if some of the fields are not part of an index. Instead use ABAP SORT + DELETE ADJACENT DUPLICATES on an internal table, to delete duplicate rows

Reward if useful.

Regards,

Sankar

Re: Database

Former Member — Fri, 03 Aug 2007 10:06:55 GMT

Try to avoid nested selects and selects inside loops.

Reduce the number of entries in your driver table if you are using FOR ALL ENTRIES.

Replace SELECT..ENDSELECT by SELECT SINGLE wherever possible.

Specify all/most of the key fields to SELECT wherever possible.

Re: Database

Former Member — Fri, 03 Aug 2007 10:19:12 GMT

This is my code yar. do needful.

REPORT ZSAB_FI_DOC.

TABLES : bseg,LFA1,EKKO.

TYPE-POOLS : slis.

Data Declarations

DATA : fcat TYPE slis_t_fieldcat_alv,

it_sort TYPE SLIS_T_SORTINFO_ALV ,

wa_sort type slis_sortinfo_alv,

ls_fcat LIKE LINE OF fcat,

fheader TYPE TABLE OF slis_listheader WITH NON-UNIQUE DEFAULT KEY

WITH HEADER LINE INITIAL SIZE 0.

DATA :i_repid LIKE sy-repid,

gs_layout TYPE slis_layout_alv,

week type n.

DATA: l_index TYPE sy-tabix,

output(20) type c.

*Internal Tables

DATA : BEGIN OF IT_BSEG OCCURS 0,

BELNR LIKE BSEG-BELNR,

EBELN LIKE BSEG-EBELN,

EBELP LIKE BSEG-EBELP,

LIFNR LIKE BSEG-LIFNR,

GJAHR like BSEG-GJAHR,

ZBEL1 LIKE BSEG-BELNR,

END OF IT_BSEG.

DATA : BEGIN OF IT_BSEG1 OCCURS 0,

BELNR LIKE BSEG-BELNR,

EBELN LIKE BSEG-EBELN,

EBELP LIKE BSEG-EBELP,

LIFNR LIKE BSEG-LIFNR,

VALUT LIKE BSEG-VALUT,

MATNR LIKE BSEG-MATNR,

WERKS LIKE BSEG-WERKS,

MENGE LIKE BSEG-MENGE,

MEINS LIKE BSEG-MEINS,

GJAHR like BSEG-GJAHR,

ZBEL1 LIKE BSEG-BELNR,

NAME1 LIKE LFA1-NAME1,

DMBTR LIKE BSEG-DMBTR,

END OF IT_BSEG1.

DATA : BEGIN OF IT_EKKO OCCURS 0,

EBELN LIKE EKKO-EBELN,

LIFNR LIKE EKKO-LIFNR,

END OF IT_EKKO.

DATA : BEGIN OF IT_LFA1 OCCURS 0,

LIFNR LIKE LFA1-LIFNR,

NAME1 LIKE LFA1-NAME1,

END OF IT_LFA1.

DATA: WA_BSEG1 LIKE LINE OF IT_BSEG1,

WA_EKKO LIKE LINE OF IT_EKKO,

WA_LFA1 LIKE LINE OF IT_LFA1.

FIELD-SYMBOLS: <fs1> like line of it_bseg,

<fs2> like line of it_bseg1.

SELECTION-SCREEN BEGIN OF BLOCK bk1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: zbel FOR bseg-belnr OBLIGATORY.

SELECT-OPTIONS: zfi FOR bseg-GJAHR OBLIGATORY.

SELECTION-SCREEN END OF BLOCK bk1.

zbel = zbel-low.

zfi = zfi-low.

perform edit_mask.

gs_layout-colwidth_optimize = 'X'.

select belnr ebeln GJAHR

from bseg

into corresponding fields of TABLE it_bseg

where belnr in zbel and

GJAHR in zfi.

if it_bseg[] is initial.

exit.

endif.

loop at it_bseg.

move it_bseg-belnr to it_bseg-zbel1.

modify it_bseg.

endloop.

SELECT BELNR

VALUT

EBELN

EBELP

LIFNR

MATNR

WERKS

MENGE

MEINS

GJAHR

DMBTR

FROM BSEG

INTO CORRESPONDING FIELDS OF TABLE IT_BSEG1

for all entries in it_bseg

WHERE EBELN = IT_BSEG-EBELN and

GJAHR = IT_BSEG-GJAHR.

*loop at it_bseg.

*loop at it_bseg1.

if it_bseg-zbel1 = it_bseg1-belnr.
move it_bseg-zbel1 to it_bseg1-zbel1.
modify it_bseg1.
endif.

if it_bseg1-ebeln = it_bseg-ebeln.
move it_bseg-zbel1 to it_bseg1-zbel1.
modify it_bseg1.
endif.

*endloop.

*sort it_bseg by ebeln.

*sort it_bseg1 by ebeln.

*loop at it_bseg1.

read table it_bseg with key ebeln = it_bseg1-ebeln binary search.
if sy-subrc = 0.
move it_bseg-zbel1 to it_bseg1-zbel1.
modify it_bseg1.
endif.

*endloop.

sort it_bseg by ebeln.

sort it_bseg1 by ebeln.

loop at it_bseg1 ASSIGNING <fs2>.

READ TABLE it_bseg assigning <fs1> with key ebeln = <fs2>-ebeln .

if sy-subrc = 0.

<fs2>-zbel1 = <fs1>-zbel1.

endif.

endloop.

if not it_bseg1[] is initial.

*- Ekko

SELECT EBELN LIFNR

FROM EKKO INTO table it_ekko

FOR ALL ENTRIES IN it_bseg1

WHERE EBELN = IT_BSEG1-EBELN.

if sy-subrc = 0.

sort it_ekko by ebeln lifnr.

*-Lfa1

SELECT LIFNR NAME1 FROM LFA1

INTO table it_lfa1

FOR ALL ENTRIES IN it_ekko

WHERE LIFNR = IT_EKKO-LIFNR.

if sy-subrc = 0.

sort it_lfa1 by lifnr.

endif.

loop at it_bseg1.

Read table it_ekko with key ebeln = it_bseg1-ebeln.

if sy-subrc eq 0.

read table it_lfa1 with key lifnr = it_ekko-lifnr.

if sy-subrc eq 0.

it_bseg1-lifnr = it_ekko-lifnr.

it_bseg1-name1 = it_lfa1-name1.

modify it_bseg1 transporting lifnr.

modify it_bseg1 transporting name1.

endif.

endloop.

*LOOP AT IT_BSEG1.

IF IT_BSEG1-LIFNR IS INITIAL.

SELECT SINGLE EBELN LIFNR
FROM EKKO INTO (IT_BSEG1-EBELN , IT_BSEG1-LIFNR)
WHERE EBELN = IT_BSEG1-EBELN.

ENDIF.

SELECT SINGLE LIFNR NAME1 FROM LFA1
INTO (IT_BSEG1-LIFNR , IT_BSEG1-NAME1)
WHERE LIFNR = IT_BSEG1-LIFNR.

MODIFY IT_BSEG1.

*ENDLOOP.

PERFORM EDIT_MASK.

PERFORM EDIT_MASK1.

loop at it_bseg1.

if it_bseg1-belnr(1) NE '4'.

delete it_bseg1.

endif.

endloop.

i_repid = sy-repid.

PERFORM fieldcat.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = i_repid

is_layout = gs_layout

it_fieldcat = fcat

i_save = 'A'

TABLES

t_outtab = it_bseg1

EXCEPTIONS

program_error = 1

OTHERS = 2.

&----

*& Form fieldcat

&----

text

----

FORM fieldcat.

MOVE sy-repid TO i_repid.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'ZBEL1'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'Document'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'BELNR'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'Document'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'GJAHR'.

ls_fcat-outputlen = 4.

ls_fcat-seltext_m = 'F.Year'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'EBELN'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'PO Number'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'EBELP'.

ls_fcat-outputlen = 2.

ls_fcat-seltext_m = 'Item'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'LIFNR'.

ls_fcat-outputlen = 2.

ls_fcat-seltext_m = 'Vendor'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'NAME1'.

ls_fcat-outputlen = 20.

ls_fcat-seltext_m = 'Description'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'MATNR'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'Material'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'VALUT'.

ls_fcat-outputlen = 10.

ls_fcat-seltext_m = 'Posting Date'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'WERKS'.

ls_fcat-outputlen = 4.

ls_fcat-seltext_m = 'Plant'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'MENGE'.

ls_fcat-outputlen = 5.

ls_fcat-seltext_m = 'Qty'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'MEINS'.

ls_fcat-outputlen = 3.

ls_fcat-seltext_m = 'Un'.

APPEND ls_fcat TO fcat.

ls_fcat-tabname = 'IT_BSEG1'.

ls_fcat-fieldname = 'DMBTR'.

ls_fcat-outputlen = 3.

ls_fcat-seltext_m = 'Amount'.

APPEND ls_fcat TO fcat.

endform.

FORM EDIT_MASK1 .

loop at it_bseg1.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

INPUT = it_bseg1-belnr

IMPORTING

OUTPUT = it_bseg1-belnr.

modify it_bseg1.

endloop.

ENDFORM.

form edit_mask.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = zbel

IMPORTING

output = zbel.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_OUTPUT'

EXPORTING

input = zfi

IMPORTING

output = zfi.

endform.

Re: Database

Former Member — Fri, 03 Aug 2007 10:29:29 GMT

Hi Raja Ram,

I had a look at your code,

Your code is good but for some minor tweakings that are to be done.

1) Avoid usage of "INTO CORRESPONDING FIELDS OF" in the SELECT statement. Try to realign your internal table and just send the selected fields into that so that if you send 3 fields if they are in the same order then only those 3 fields gets updated in the internal table and the rest remain blank.

2) Try using explicit work areas instead of using the header line of the internal tables.

Possibly your code's performance shall increase with these tweakings.

<b>Reward points if this helps,</b>

Kiran

Re: Database

Former Member — Fri, 03 Aug 2007 10:31:23 GMT

hi it can be done by basis people

Re: Database

Former Member — Fri, 03 Aug 2007 10:39:35 GMT

What else we can use instead of INTO CORRESPONDING FIELDS OF

Regards

Rajaram

Re: Database

Former Member — Fri, 03 Aug 2007 10:44:06 GMT

Hi raja

U r code is good but use all the keyfields when u r retrieving the data from bseg and mseg tables as possible

Best Regds,

Kiran.M