Performance problem in EDIDS,EDIDC tables

[Former Member]

Hi,

I've got to fetch the total number of idocs per message type from EDIDC and EDIDS tables.And the in selection screen the user will enter only dates when its created and this is not a primary key in EDIDC....from EDIDC table i'll have to get all the idocs and message type and count the total number of records per message type and the output will be only message type column and total number of EDIDC records and EDIDs records per message type....

As we know when we dont enter the primary key in the selection screen its taking lot of time to fetch the data from EDIDC table and more over the EDIDS does'nt have a field called MESTYP in its table so i need to read the EDIDC yet again to popuated the EDIDS message type in my second internal table........this again is eating lot of time.............How to solve this problem.......Plz come up with good solutions.....The logic i'm using is as follows.........

TYPE-POOLS: slis.

TABLES: edidc, edids.

TYPES: BEGIN OF tp_edidc,

docnum LIKE edidc-docnum,

direct LIKE edidc-direct,

status LIKE edidc-status,

rcvprn LIKE edidc-rcvprn,

mestyp LIKE edidc-mestyp,

credat LIKE edidc-credat,

cretim LIKE edidc-cretim,

END OF tp_edidc.

TYPES: BEGIN OF tp_edids,

docnum LIKE edids-docnum,

status LIKE edids-status,

END OF tp_edids.

TYPES: BEGIN OF tp_output,

mestyp LIKE edidc-mestyp,

records TYPE i,

END OF tp_output.

TYPES: BEGIN OF tp_final,

mestyp LIKE edidc-mestyp,

record TYPE i,

record1 TYPE i,

END OF tp_final.

TYPES: BEGIN OF tp_output1,

mestyp LIKE edidc-mestyp,

records TYPE i,

END OF tp_output1.

DATA: g_tab_edidc TYPE STANDARD TABLE OF tp_edidc,

g_wa_edidc TYPE tp_edidc,

g_tab_edids TYPE STANDARD TABLE OF tp_edids,

g_wa_edids TYPE tp_edids,

g_tab_output TYPE STANDARD TABLE OF tp_output,

g_wa_output TYPE tp_output,

g_tab_output1 TYPE STANDARD TABLE OF tp_output,

g_wa_output1 TYPE tp_output,

g_tab_final TYPE STANDARD TABLE OF tp_final,

g_wa_final TYPE tp_final,

count TYPE i,

g_wa_fieldcat TYPE slis_fieldcat_alv,

g_tab_fieldcat TYPE slis_t_fieldcat_alv,

g_layout TYPE slis_layout_alv.

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

SELECT-OPTIONS: s_direct FOR edidc-direct,

s_status FOR edidc-status,

s_credat FOR edids-credat,

s_cretim FOR edids-cretim,

s_mestyp FOR edidc-mestyp,

s_rcvprn FOR edidc-rcvprn.

SELECTION-SCREEN: END OF BLOCK b1.

START-OF-SELECTION.

PERFORM get_data.

PERFORM populate_entries_edidc_edids.

PERFORM populate_output.

PERFORM populate_output1.

PERFORM populate_final_output.

IF NOT g_tab_output[] IS INITIAL.

PERFORM layout.

PERFORM display_output.

ELSE.

MESSAGE i208(zz) WITH 'No Record Exist'.

EXIT.

ENDIF.

&----

---

*& Form get_data

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM get_data .

SELECT docnum

direct

status

rcvprn

mestyp

credat

cretim FROM edidc INTO TABLE g_tab_edidc

WHERE direct IN s_direct AND

status IN s_status AND

rcvprn IN s_rcvprn AND

mestyp IN s_mestyp AND

credat IN s_credat AND

cretim IN s_cretim.

IF NOT g_tab_edidc IS INITIAL.

SELECT docnum

status FROM edids INTO TABLE g_tab_edids

FOR ALL ENTRIES IN g_tab_edidc

WHERE docnum = g_tab_edidc-docnum.

ENDIF.

ENDFORM. " get_data

&----

---

*& Form populate_entries_edidc_edids

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM populate_entries_edidc_edids .

SORT g_tab_edidc BY mestyp .

LOOP AT g_tab_edidc INTO g_wa_edidc.

g_wa_output-mestyp = g_wa_edidc-mestyp.

APPEND g_wa_output TO g_tab_output.

CLEAR: g_wa_edidc,g_wa_edids.

ENDLOOP.

LOOP AT g_tab_edids INTO g_wa_edids.

READ TABLE g_tab_edidc INTO g_wa_edidc WITH

KEY docnum = g_wa_edids-docnum.

IF sy-subrc = 0.

g_wa_output1-mestyp = g_wa_edidc-mestyp.

APPEND g_wa_output1 TO g_tab_output1.

ENDIF.

CLEAR: g_wa_edidc,g_wa_edids.

ENDLOOP.

ENDFORM. " populate_entries_edidc_edids

&----

---

*& Form display_output

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM display_output .

CLEAR g_wa_fieldcat.

g_wa_fieldcat-col_pos = '1'.

g_wa_fieldcat-fieldname = 'MESTYP'.

g_wa_fieldcat-seltext_l = 'Message Type'.

g_wa_fieldcat-tabname = 'G_TAB_FINAL'.

APPEND g_wa_fieldcat TO g_tab_fieldcat.

CLEAR g_wa_fieldcat.

g_wa_fieldcat-col_pos = '2'.

g_wa_fieldcat-fieldname = 'RECORD'.

g_wa_fieldcat-seltext_l = 'EDIDC Total Records'.

g_wa_fieldcat-tabname = 'G_TAB_FINAL'.

APPEND g_wa_fieldcat TO g_tab_fieldcat.

CLEAR g_wa_fieldcat.

g_wa_fieldcat-col_pos = '3'.

g_wa_fieldcat-fieldname = 'RECORD1'.

g_wa_fieldcat-seltext_l = 'EDIDS Total Records'.

g_wa_fieldcat-tabname = 'G_TAB_FINAL'.

APPEND g_wa_fieldcat TO g_tab_fieldcat.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

i_callback_program = sy-repid

is_layout = g_layout

it_fieldcat = g_tab_fieldcat

TABLES

t_outtab = g_tab_final

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2

.

IF sy-subrc 0.

MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO

WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.

ENDIF.

ENDFORM. " display_output

&----

---

*& Form LAYOUT

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM layout .

g_layout-colwidth_optimize = 'X'.

ENDFORM. " LAYOUT

&----

---

*& Form populate_output

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM populate_output .

LOOP AT g_tab_output INTO g_wa_output.

AT NEW mestyp.

count = 0.

ENDAT.

count = count + 1.

AT END OF mestyp.

g_wa_output-records = count.

MODIFY g_tab_output INDEX sy-tabix FROM g_wa_output.

ENDAT.

CLEAR: g_wa_output.

ENDLOOP.

DELETE g_tab_output WHERE records IS INITIAL.

ENDFORM. " populate_output

&----

---

*& Form populate_output1

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM populate_output1 .

SORT g_tab_output1 BY mestyp.

LOOP AT g_tab_output1 INTO g_wa_output1.

AT NEW mestyp.

count = 0.

ENDAT.

count = count + 1.

AT END OF mestyp.

g_wa_output1-records = count.

MODIFY g_tab_output1 INDEX sy-tabix FROM g_wa_output1.

ENDAT.

CLEAR: g_wa_output1.

ENDLOOP.

DELETE g_tab_output1 WHERE records IS INITIAL.

ENDFORM. " populate_output1

&----

---

*& Form populate_final_output

&----

---

text

-

---

--> p1 text

<-- p2 text

-

---

FORM populate_final_output .

LOOP AT g_tab_output INTO g_wa_output.

READ TABLE g_tab_output1 INTO g_wa_output1 WITH KEY mestyp = g_wa_output.

IF sy-subrc = 0.

g_wa_final-mestyp = g_wa_output-mestyp.

g_wa_final-record = g_wa_output-records.

g_wa_final-record1 = g_wa_output1-records.

APPEND g_wa_final TO g_tab_final.

ENDIF.

CLEAR: g_wa_output,g_wa_output1.

ENDLOOP.

ENDFORM. " populate_final_output

[Former Member]

I've resolved it myself

[Former Member]

Hi,

EDIDC table has a secondary index on the Message type. So you can redesign your query and put the MESTYP first in the where clause and then the rest. Once you have all the Idoc numbers from EDIDC, then using those you can select from EDIDS

SELECT docnum
direct
status
rcvprn
mestyp
credat
cretim FROM edidc INTO TABLE g_tab_edidc
WHERE 
mestyp IN s_mestyp AND         " This way the index EDIDC~3 will be used.
credat IN s_credat AND  " Put this second, because there is a index on creation date as well.
direct IN s_direct AND
status IN s_status AND
rcvprn IN s_rcvprn AND
cretim IN s_cretim. 

The order in the where clause is important selecting the data using indices.

Also change this statment

IF NOT g_tab_edidc IS INITIAL.    " This only checks the work area

to

IF NOT g_tab_edidc[] IS INITIAL.  " This will check the table

I dont think you need this loop .

LOOP AT g_tab_edidc INTO g_wa_edidc.

g_wa_output-mestyp = g_wa_edidc-mestyp.

APPEND g_wa_output TO g_tab_output.

CLEAR: g_wa_edidc,g_wa_edids.

ENDLOOP.

Because you are already reading the internal table with EDIDC recrods while looping at EDIDS internal table. Also use binary search when reading from the table. ( sort the edidc and edids by docnum)

And also , the best would be to join the EDIDC and EDIDS table inntead of selecting them in separate queries.

Hope this helps

regards,

Advait

Edited by: Advait Gode on Nov 24, 2008 9:23 AM

[yuri_ziryukin]

> The order in the where clause is important selecting the data using indices.

This is not true, please do not confuse the community.

[Former Member]

hi yuri, got any idea on my requirments?

[yuri_ziryukin]

hi yuri, got any idea on my requirments?

Sorry, this is a typical functional question, has little to do with the performance.

[Former Member]

I've resolved it myself

[Former Member]

hi khan did you finish this cutom report already? can you share me your code? i also need to get the total number of inbound and outbound proccessing base on message types, the difference is i need to display it per hour, hope you can help me