topic Re: Performance Issue.. in Application Development and Automation Discussions

Performance Issue..

Former Member — Wed, 20 Dec 2006 09:57:43 GMT

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

Re: Performance Issue..

Former Member — Wed, 20 Dec 2006 10:01:05 GMT

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

Re: Performance Issue..

Former Member — Wed, 20 Dec 2006 10:04:51 GMT

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

Re: Performance Issue..

Former Member — Wed, 20 Dec 2006 10:23:25 GMT

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

Re: Performance Issue..

Former Member — Wed, 20 Dec 2006 10:50:12 GMT

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

Re: Performance Issue..

Former Member — Wed, 20 Dec 2006 10:58:12 GMT

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

Re: Performance Issue..

christian_wohlfahrt — Wed, 20 Dec 2006 11:19:45 GMT

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

Re: Performance Issue..

Former Member — Wed, 20 Dec 2006 15:16:55 GMT

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

Rob

Re: Performance Issue..

Former Member — Thu, 21 Dec 2006 04:05:49 GMT

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 .

Re: Performance Issue..

Sathish — Thu, 21 Dec 2006 06:57:30 GMT

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.