Data Retrieval Huge database - Urgent

[Former Member]

Hi All

I ave abit of a problem, I am working in the SAP FS-RI module. Financial Reinsurance. I am helping with closing and continuit testing.

For most of the reports I am creating things are fine, It is when I am going against the Account Header and Account Detail tables I am running into huge performance issues. Thes accounts detail table has over 200 million records and any time I try to get data I am running into a VERY LONG RUNNING TIME.

I try to reduce the pool of treaties as much as possible before going after the accounting information. I then join the account header and detail tables. I loop at my table of treaties and try to reduce the amount of data with my where and select clauses. Someone here mentioned using indexes but I am unfamillar with that.

I am using a standard table as the account posting can have many for each treaty. I will upload a sample of one of my reports. But if ANYONE has any idea's please let me know THIS IS VERY URGENT

Thanks

[Former Member]

Hi All

I ave abit of a problem, I am working in the SAP FS-RI module. Financial Reinsurance. I am helping with closing and continuit testing.

For most of the reports I am creating things are fine, It is when I am going against the Account Header and Account Detail tables I am running into huge performance issues. Thes accounts detail table has over 200 million records and any time I try to get data I am running into a VERY LONG RUNNING TIME.

I try to reduce the pool of treaties as much as possible before going after the accounting information. I then join the account header and detail tables. I loop at my table of treaties and try to reduce the amount of data with my where and select clauses. Someone here mentioned using indexes but I am unfamillar with that.

I am using a standard table as the account posting can have many for each treaty. I will upload a sample of one of my reports. But if ANYONE has any idea's please let me know THIS IS VERY URGENT

Thanks

[Former Member]

Can we see the select that is causing the biggest problem?

Rob

[Former Member]

Hi

I have stripped out most of the program NOT relating to the select, When this prg is run with the select parameters I.E. certain treaty numbers it runs fine HOWEVER they want it to run with like 8000 treaties that is when I hit the BIG PROBLEMS. hERE IS THE PROGRAM MINUS THE alv ETC ETC

• ----- T A B L E S -----

TABLES: /msg/rvtg, "Reinsurance Treaty (Master Data)

/msg/rvtgbestko, "Treaty Section Header

t100, "Messages

/msg/rnodebez, "Nodes and sheets for hierarchy: texts

/msg/rgrpzu_best, "Group-Section Assignment

/msg/rearcurbes2, "Development Patterns for the Section

/mrg/grvtpr. "Result Responsibility having validity periods

• ---- I n t e r n a l t a b l e s -----

DATA: t_out LIKE st_out OCCURS 0 WITH HEADER LINE.

SELECT-OPTIONS:

sel_bkr FOR lv_bukrs OBLIGATORY DEFAULT '1001'.

SELECTION-SCREEN:

SKIP 1.

PARAMETERS:

pv_excl AS CHECKBOX DEFAULT 'X'.

SELECTION-SCREEN BEGIN OF BLOCK blk2 WITH FRAME TITLE text-006.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 22(26) text-011.

SELECT-OPTIONS:

sel_trty FOR lv_ttry.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 22(26) text-021.

SELECT-OPTIONS:

entrycd FOR lv_entry.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 22(26) text-007.

SELECT-OPTIONS:

sel_aseg FOR lv_actseg.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK blk2.

SELECTION-SCREEN BEGIN OF BLOCK blk3 WITH FRAME TITLE text-016.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 22(26) text-020.

SELECT-OPTIONS:

sel_fiyr FOR lv_finyr.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 22(26) text-010.

SELECT-OPTIONS:

sel_unyr FOR lv_undyr.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 22(26) text-017.

SELECT-OPTIONS:

sel_ocyr FOR lv_occyr.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK blk3.

AT SELECTION-SCREEN.

*

DESCRIBE TABLE sel_aseg LINES lin.

IF ( lin > 0 ).

IF NOT sel_aseg-low CP '#P#E***'.

MESSAGE e004.

ENDIF.

IF NOT sel_aseg-high IS INITIAL.

IF NOT sel_aseg-high CP '#P#E***'.

MESSAGE e004.

ENDIF.

ENDIF.

IF ( lin > 1 ).

LOOP AT sel_aseg.

IF NOT sel_aseg-low CP '#P#E***'.

MESSAGE e006.

ENDIF.

ENDLOOP.

ENDIF.

ENDIF.

• *----- S T A R T O F S E L E C T I O N -----

*

START-OF-SELECTION.

PERFORM gather_details.

• &---------------------------------------------------------------------*

• & Form gather_details

• &---------------------------------------------------------------------*

• text

• ----------------------------------------------------------------------*

• --> p1 text

• <-- p2 text

• ----------------------------------------------------------------------*

FORM gather_details .

SELECT /msg/rvtg_{bukrs /msg/rvtgbestko}vtgartnr /msg/rvtgbestko_{brchnr /msg/rbu}brchnr

/msg/rvtgbest_{fas_class /msg/rvtgper}vtgjj /msg/rabr_{biljj /msg/rbu}zjj

/msg/rbu_{anfjj /msg/rvtgper}status /msg/rvtgbestko_{/mrg/pe_status /msg/rearcur_hd}/mrg/tu_status

/msg/rvtgper_{vtgnr /msg/rvtgbestko}bestnr /msg/rvtg_{ze_vtgnr /msg/rbu}buconr

/msg/rabr_{schaekz /msg/rabr}ow_whgnr /msg/rbu_{ow_betr /msg/rvtg}vtgartgrp

/msg/rvtg~vtgtypnr

INTO (t_out-bukrs, t_out-vtgartnr, t_out-brchnr, t_out-brchnr_post,

t_out-fas_class, t_out-vtgjj, t_out-biljj, t_out-zjj, t_out-anfjj,

t_out-status, t_out-/mrg/pe_status, t_out-/mrg/tu_status, t_out-vtgnr, t_out-bestnr,

t_out-ze_vtgnr, t_out-buconr, t_out-schaekz, t_out-ow_whgnr, t_out-ow_betr,

t_out-vtgartgrp, t_out-vtgtypnr)

FROM /msg/rvtg AS /msg/rvtg

INNER JOIN /msg/rvtgbestko AS /msg/rvtgbestko

ON /msg/rvtg_{vtgnr = /msg/rvtgbestko}vtgnr

INNER JOIN /msg/rvtgper AS /msg/rvtgper

ON /msg/rvtgper_{vtgnr = /msg/rvtg}vtgnr

INNER JOIN /msg/rvtgbest AS /msg/rvtgbest

ON /msg/rvtgbest_{vtgnr = /msg/rvtgper}vtgnr

AND /msg/rvtgbest_{vtgjj = /msg/rvtgper}vtgjj

INNER JOIN /msg/rearcur_hd AS /msg/rearcur_hd

ON /msg/rearcur_hd_{vtgnr = /msg/rvtgbest}vtgnr

AND /msg/rearcur_hd_{buchztr_jj_start = /msg/rvtgbest}vtgjj

INNER JOIN /msg/rabr AS /msg/rabr

ON /msg/rabr_{vtgnr = /msg/rvtgbest}vtgnr

INNER JOIN /msg/rbu AS /msg/rbu

ON /msg/rbu_{abrnr = /msg/rabr}abrnr

AND /msg/rbu_{vtgnr = /msg/rabr}vtgnr

WHERE /msg/rvtg~vtgnr IN sel_trty

AND buconr IN entrycd.

APPEND t_out.

ENDSELECT.

LOOP AT t_out.

SELECT SINGLE vtgartgrbez FROM /msg/rvtgagrbez INTO t_out-nat_txt

WHERE vtgartgr = t_out-vtgartgrp

AND spras = 'EN'.

SELECT SINGLE vtgtypbez FROM /msg/rvtgtypbez INTO t_out-cat_txt

WHERE vtgtypnr = t_out-vtgtypnr

AND spras = 'EN'.

SELECT SINGLE grp_nr INTO t_out-act_seg

FROM /msg/rgrpzu_best

WHERE vtgnr = t_out-vtgnr

AND grp_nr LIKE 'PE%'.

SELECT SINGLE grp_nr INTO t_out-act_seg

FROM /msg/rgrpzu_best

WHERE vtgnr = t_out-vtgnr

AND grp_nr LIKE 'PE%'.

SELECT SINGLE nodebez INTO t_out-brchnr_txt

FROM /msg/rnodebez

WHERE spras = 'EN'

AND hierclass = 'COB'

AND node = t_out-brchnr..

SELECT SINGLE hrobjid INTO t_out-rr_seg

FROM /mrg/grvtpr

WHERE vtgnr = t_out-vtgnr

AND otype = 'O'

AND competence = 'T2'.

CALL FUNCTION '/MSG/R_S_GET_BEZ_FOR_OBJID'

EXPORTING

pv_otype = 'O'

pv_objid = t_out-rr_seg

pv_wegid = 'MSG'

IMPORTING

pv_short = w_hld

EXCEPTIONS

not_found = 1

OTHERS = 2.

CONCATENATE t_out-rr_txt w_hld INTO t_out-rr_txt SEPARATED BY space.

CLEAR w_hld.

WRITE t_out-vtgnr TO w_ttyhld NO-ZERO .

MOVE w_ttyhld TO t_out-vtgnr.

PERFORM get_petext USING t_out-/mrg/pe_status.

MOVE w_petxt TO t_out-pe_txt.

PERFORM get_typno USING t_out-vtgartnr.

MOVE w_hld_typ TO t_out-rety_txt.

MODIFY t_out.

CLEAR /mrg/grvtpr-hrobjid.

ENDLOOP.

[Former Member]

Some thoughts:

Have you run a performance trace to identify which of these selects is causing the problem. I expect it's the first one, but a trace would help.

You are joining 6 tables. I don't know if this would cause a problem, but many in this forum will suggest breaking it into two or more joins.

I don't have these tables in our system, so I don't know their structure, but VTGNR and BUCONR the first two fields in a key of table /msg/rvtg?

What are the contents of sel_trty and entrycd? In particular, are either of them empty? If they are not empty do they only contain a number of single values with SIGN = 'I' and OPTION = 'EQ'? If they can contain exclusions or not equal conditions or patterns, that can slow down the selection.

Rob

[Former Member]

Dear George,

While writing joins on multiple tables, do not user SELECT...ENDSELECT. User Select <field list> into corresponding fields of <internal table> from <table1> join <table2> on ....Just change the select statement and see the performence.

Regards,

Prasanth