Performance Issue..

[Former Member]

Hi all,

I have major performance issue for the below stmt.

• Select cases in vbrk that have bills that matches cases in itn_cases,

• including canceled bills [ For Final and Interim Billing ]

SELECT

ishfalnr

ishfalrn

fkdat

ishlfdbew

vbeln

FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE itn_vbrk

FOR ALL ENTRIES IN itn_cases

WHERE

isheinri = p_einri

AND ishfalnr = itn_cases-falnr

AND sfakn EQ SPACE.

The above statement is related to SAP Healthcare table data selection... we have more than 7 million records in VBRK table...

This above statement is taking almost 85% of the execution time and its running in the background for more than 2 hours..... <b>we have secondary index defined already on isheinri & ishfalnr fields...</b>

Please advise me, as its very High Priority issue here... Thanks in advance....

[Former Member]

arrenge the order of field in same as u fetch it from table.not use into CORRESPONDING fields.

SELECT
ishfalnr
ishfalrn
fkdat
ishlfdbew
vbeln
FROM vbrk
INTO TABLE itn_vbrk
FOR ALL ENTRIES IN itn_cases
WHERE
isheinri = p_einri
AND ishfalnr = itn_cases-falnr
AND sfakn EQ SPACE.

null

[Former Member]

hi,

1) declare ur internal table in the seq of fields in the db table

2) select the fields in the seq as done in internal table

3) avoid using into corresponding fields of

4) avoid using NE, EQ instead use symbols =, <>

5) check if itn_cases has data

6) if it has reduce your fetch criteria.

hope these help

santhosh

[Former Member]

I think you can try to fetch data using the variation in SELECT statement as to fetch x records. Please check the SE36 transactions related to LDB's you would get an idea how SAP tries to get the particular data. Infact, you can check if there is any LDB available for table VBRK.

Regards

Anurag

[Former Member]

Hi,

1. Remove INTO CORRESPONDING FIELDS

2. if the records are being repeated in first table i.e while using for all entries, remove duplicate entries from the itab and move them into third internal table

3. sort the itab and go for binary search based on the sorted field

4. check the internal table before going for the for all entries

5. try to use Join condition if ther tables are small and having more no of key relationship

thanks

Shiva

[Former Member]

Hi Robert ,

Please try doing the following

1. Do not user INTO CORRESPONDING FIELDS , define the table in such a way that the sequence of the fields are same as that in table and use into table.

2. Please check if itn_casees has value or not , if it is balnk then it will seelct all the records in the table.

Regards

Arun

[christian_wohlfahrt]

Hi Robert,

beside avoiding 'corresponding fields' (which might make the data transfer faster): check your index. The index VBRK~N is delivered by SAP, but on our system it's not created on database. Meaning: it's defined, but not used. It should be activated, but please check, if everything is OK with the index. Second question: is the index used? Make a short SQL-trace (I think you need the starting, but you don't need to wait for the end of the select) and have a look, if the index is used. In case it isn't used, a missing / old statistic might be the reason. This can be updated with DB20.

Regards,

Christian

[Former Member]

You may have to have the database statistics rebuilt as well.

Rob

[Former Member]

Robert,

1. if possible(if your selection fields and internal table fields are same count)

you can remove move-corresponding and use into table

2.you can use "package size"

(if selection fields and internal table fields are same.)

SELECT

ishfalnr

ishfalrn

fkdat

ishlfdbew

vbeln

FROM vbrk

package size '30000'

appending FIELDS OF TABLE itn_vbrk

FOR ALL ENTRIES IN itn_cases

WHERE

isheinri = p_einri

AND ishfalnr = itn_cases-falnr

AND sfakn EQ SPACE.

Pls. mark for useful .

[Sathish]

You can use OPEN CURSOR for improving the performance. Define a packgae size recommended 500 or 1000.

OPEN CURSOR WITH HOLD db_cursor FOR

SELECT

ishfalnr

ishfalrn

fkdat

ishlfdbew

vbeln

FROM vbrk

FOR ALL ENTRIES IN itn_cases

WHERE

isheinri = p_einri

AND ishfalnr = itn_cases-falnr

AND sfakn EQ SPACE.

DO.

FETCH NEXT CURSOR db_cursor

INTO CORRESPONDING FIELDS OF TABLE itn_vbrk

PACKAGE SIZE g_package_size.

IF sy-subrc NE 0.

CLOSE CURSOR db_cursor.

EXIT.

ENDIF.

{

have your processing block here

}

ENDDO.

This would definitely improve the performance. If it helps do reward.