Enhance select statement (takes over 3 MINs to execute)

[former_member329853]

select

AFVC~PERNR AFIH~IWERK AFIH~KUNUM AUFK~AUFNR AFIH~AUFNR AFKO~AUFNR AFVC~VORNR AFVC~LTXA1 AFVV~ARBEI AFVV~ISMNW AFVV~ARBEH AFVC~LARNT AFIH~SERMAT AFIH~SERIALNR AFIH~DEVICEID AFIH~ADDAT AFVV~IEDD AUFK~ERDAT vbak~ERDAT as so_ERDAT vbak~VBELN

      AUFK~VAPLZ AUFK~WAWRK

      AFIH~INGPR AFVC~AUFPL  AFVV~APLZL AFVV~AUFPL

  INTO CORRESPONDING FIELDS OF  TABLE itab

   from ( AUFK

          inner join AFIH

          on  AFIH~AUFNR = AUFK~AUFNR

          inner join AFKO

          on  AFKO~AUFNR = AUFK~AUFNR

          inner join AFVC

          on  AFVC~AUFPL = AFKO~AUFPL

          inner join AFVV

          on  AFVV~APLZL = AFVC~APLZL

          and AFVV~AUFPL = AFVC~AUFPL

          LEFT OUTER JOIN vbak

          on  VBAK~AUFNR = AUFK~AUFNR )

        where AFIH~IWERK in SP$00002

          and AFIH~INGPR in SP$00003

*         and AFIH~ADDAT in SP$00005

          and AFVC~PERNR in SP$00001

          and AFVC~LARNT in SP$00004

          and AFVV~IEDD  in SP$00006

          and AUFK~ERDAT in SP$00007

          and AFVC~PERNR ne ''

[former_member226239]

Try to do the following.....

1. Check whether you have any custom index on the Field AUFK~ERDAT (there is no standard index on it), if not try to create one. This will improve the performance a lot in your case.

2. Try to replace the 'INTO CORRESPONDING FIELDS OF TABLE' with 'INTO TABLE'

3. Change the last inner join on AFVV condition as follows

AFVV~AUFPL = AFVC~AUFPL

and AFVV~APLZL = AFVC~APLZL

Let me know how it goes.

-Chandra

[former_member329853]

NO CHANGE, STILL NOT WORKING

[former_member329853]

NOTE, I did not create any index, I don't know how it works

[Sandra_Rossi]

If you want help about SQL performance, you must also :

1) provide the input values

2) provide the EXPLAIN

[raymond_giuseppi]

Did you create any Z-index on VBAK by order number AUFNR (else a full table scan?)

Also execute a SQL trace thru ST05 and analyze access path

There is (was, no longer released?) also a note on 185530 - Performance: Customer developments in SD

Regards,

Raymond

[raymond_giuseppi]

Found this note for index on VBAK with AUFNR:  .371382 - Performance problems when accessing document flow (with full procedure to build an index)

Regards,

Raymond

[Jelena]

It might depend on the configuration / specific process but in our system the sales / production order link is by AFPO-KDAUF / KDPOS -> VBAP VBELN/POSNR.

+1 to Sandra. Be more specific when posting such questions. There are tons of content already on SCN regarding all kinds of performance issues. What research have you done before posting?

Next time you chose to just copy-paste a piece of code and expect the SCN members to solve the riddle for you don't be surprised if the post gets removed.

You might want to read these blogs before posting more discussions:

[former_member206439]

Better to create individual select queries with For All entries or tey to do performance trace and create a secondary index for the missing index part.