Provide alternative select statements to improve performance

[Former Member]

Hi Frnds

I want to improve the performance of my report. below statement is taking more time. Please provide any suggestions to include alternative statement for the below select statement.

SELECT H~CLMNO H~PNGUID P~PVGUID V~PNGUID AS V_PNGUID
           V~AKTIV V~KNUMV  P~POSNR  V~KATEG  AS ALTNUM   V~VERSN
         APPENDING CORRESPONDING FIELDS OF TABLE EX_WTYKEY_T
         FROM ( ( PNWTYH AS H
           INNER JOIN PNWTYV AS V ON V~HEADER_GUID   = H~PNGUID )
           INNER JOIN PVWTY  AS P ON P~VERSION_GUID  = V~PNGUID )
         FOR ALL ENTRIES IN lt_claim
        WHERE  H~PNGUID = lt_claim-pnguid
           AND V~AKTIV IN rt_AKTIV
           AND V~KATEG IN IM_ALTNUM_T.

Thanks

Amminesh.

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on May 14, 2009 11:00 AM

[jayanthi_jayaraman]

Hi,

Copy the internal table lt_claim contents to another temp internal table.

lt_claim_temp[] = lt_claim[].

sort lt_claim_temp by pnguid.

delete adjacent duplicates from lt_claim_temp comparing pnguid.

SELECT H_{CLMNO H}PNGUID P_{PVGUID V}PNGUID AS V_PNGUID

V_{AKTIV V}KNUMV P_{POSNR V}KATEG AS ALTNUM V~VERSN

APPENDING CORRESPONDING FIELDS OF TABLE EX_WTYKEY_T

FROM ( ( PNWTYH AS H

INNER JOIN PNWTYV AS V ON V_{HEADER_GUID = H}PNGUID )

INNER JOIN PVWTY AS P ON P_{VERSION_GUID = V}PNGUID )

FOR ALL ENTRIES IN lt_claim_temp

WHERE H~PNGUID = lt_claim-pnguid

AND V~AKTIV IN rt_AKTIV

AND V~KATEG IN IM_ALTNUM_T.

refresh lt_claim_temp.

[Former Member]

Explore for a possible for Data fetch BAPI.

Break the inner joins to For all entries, It may result in better performance.

Thanks,

Mathivanan.G

[Former Member]

>

> Break the inner joins to For all entries, It may result in better performance.

Aaaaarrrrgggghhhhh!!!!

Rob

[former_member182566]

>

> Aaaaarrrrgggghhhhh!!!!
> Rob

What Rob meant was: check [this sticky thread|; before following the for all entries advice.

About your question:

1) how many entries do you have in lt_claim?

2) in my version PNWTYV has no HEADER_GUID. Is there an index by HEADER_GUID?

3) in my version PVWTY has no VERSION_GUID. Is there an index by VERSION_GUID?

[former_member192616]

Hi,

could you please post ST05 summary line for that select

and the execution plan?

Kind regards,

Hermann

[former_member194613]

the 2 tables have header_guid and version_guid, which are indexed, so it should work.

Are you really sure that you need these guids and not the primary guid?

I can not see data in the tables, so I don't know what to expect.

In principle it should work, check FOR ALL ENTRIES whether empty or whether it has duplicates.

Otherwise, what do you expect? How much do you read and how long does it take?

Siegfried