Poor performance even though the index is used

[Former Member]

Hi Expert,

We are having issue with one of the select statement in production system.

However, we are not having this issue in our QAS system.

Below is the statement:

SELECT i~vbeln i~posnr i~con_quantity
     INTO TABLE gt_jksdunsolditem
     FROM jksdunsolditem AS i INNER JOIN jksdunsold AS h
                              ON h~vbeln EQ i~vbeln
     WHERE i~media_issue EQ iw_item-media_issue
       AND h~unsoldgpnr  EQ iw_head-unsoldgpnr.

And the trace result explanation:

=========== OUTPUT ==============================

SELECT T_00 ."VBELN" AS c ,T_00 ."POSNR" AS c ,T_00 ."CON_QUANTITY" AS c

FROM "JKSDUNSOLDITEM" T_00

INNER JOIN "JKSDUNSOLD" T_01 ON T_01 ."MANDT" =  @P0  AND T_01 ."VBELN" = T_00."VBELN"

WHERE T_00 ."MANDT" =  @P1  AND T_00 ."MEDIA_ISSUE" =  @P2  AND T_01."UNSOLDGPNR" =  @P3

/* R3:SAPLZFG_ISM_001_PDEX_GENERAL:2676 T:JKSDUNSOLDITEM */

/*unc. rd.*/

  SELECT

       |--Nested Loops(Inner Join, OUTER REFERENCES:([T_00].[MANDT], [T_00].[VBELN], [T_00].[POSNR], [Expr1005]) OPTIMIZED WITH UNORDERED PREFETCH)

            |--Nested Loops(Inner Join, OUTER REFERENCES:([T_01].[VBELN], [Expr1004]) OPTIMIZED WITH UNORDERED PREFETCH)

            |    |--Index Seek(OBJECT:([PRD].[prd].[JKSDUNSOLD].[JKSDUNSOLD~001] AS [T_01]), SEEK:([T_01].[MANDT]=[@P0] AND [T_01].[UNSOLDGPNR]=[@P3]) ORDERED FORWARD)

            |    |--Index Seek(OBJECT:([PRD].[prd].[JKSDUNSOLDITEM].[JKSDUNSOLDITEM~006] AS [T_00]), SEEK:([T_00].[MANDT]=[@P1] AND [T_00].[MEDIA_ISSUE]=[@P2] AND [T_00].[VBELN]=[PRD].[prd].[JKSDUNSOLD].[VBELN] as [T_01].[VBELN]) ORDERED FORWARD)

            |--Clustered Index Seek(OBJECT:([PRD].[prd].[JKSDUNSOLDITEM].[JKSDUNSOLDITEM~0] AS [T_00]), SEEK:([T_00].[MANDT]=[PRD].[prd].[JKSDUNSOLDITEM].[MANDT] as [T_00].[MANDT] AND [T_00].[VBELN]=[PRD].[prd].[JKSDUNSOLDITEM].[VBELN] as [T_00].[VBELN] AND [T_00].[POSNR]=[PRD].[prd].[JKSDUNSOLDITEM].[POSNR] as [T_00].[POSNR]) LOOKUP ORDERED FORWARD)

As you can see from above, indices required are already used.

Is there anyone that can explain the reason behind this poor performance in production server?

Thank you.

[Former Member]

Hi Abraham Bukit,

                                   Why don't you go with FOR ALL ENTRIES and avoid using INNER JOIN in a SELECT statement.

Cheers,
Dineshwar Singh Eswar.

[Former Member]

Hi Dineshwar,

Inner join is more efficient in certain condition compare to for all entries.

However, i have discover the problem.

The SQL analyzer show unoptimized query where there are 2 nested loops.

It seems like the database statistic is not updated.

Once we update the statistic, the query performance is back to normal.

Thank you.

[Former Member]

Regarding Inner Join and For All Entries discussion can be found here to support my statement above:

http://scn.sap.com/thread/1174072