SQL- Performance With VBAP table

[Former Member]

Hi Guys,

I have a query on table VBAP which fetches data based on date-time stamp and few other fields. Date-tme fields are always filled. If no plant is additionally entered, query runs for about 20 minutes. However, when user enters a plant, query runs for ever (Last run took about 16 hours). I tried tweaking the "where clause" a bit with no sign of improved performance. I really don't understand why should plant mess with the index.

Your replies are appreciated.

Thanks

Yogi

SELECT b_{vbeln b}posnr b_{uepos b}matnr b_{werks b}meins b~objnr

b_{erdat c}webaz c_{plifz e}fabkl e~pipatg

INTO TABLE pt_order

FROM vbak AS a

INNER JOIN vbap AS b

ON a_{vbeln = b}vbeln

INNER JOIN marc AS c

ON b_{matnr = c}matnr AND

b_{werks = c}werks

INNER JOIN vbuk AS d

ON a_{vbeln = d}vbeln

INNER JOIN tvst AS e

ON b_{vstel = e}vstel

WHERE a~vkorg IN s_vkorg

AND a~auart IN s_auart

AND ( ( ( b_{erdat LT s_erdat-high ) OR ( b}erdat EQ s_erdat-high

AND b~erzet LE p_totim ) )

AND ( ( b_{erdat GT s_erdat-low ) OR ( b}erdat EQ s_erdat-low

AND b~erzet GE p_frtim ) ) )

AND b~werks IN s_werks

AND b~abgru IN s_abgru

AND c~mtvfp IN s_mtvfp

AND d~cmgst IN s_cmgst.

[Former Member]

It would help if you could list the indexes and their fields for table VBAP in your system.

[Former Member]

List of indexes on VBAP

index on MANDT OBJNR

index on MANDT VPMAT & VPWRK

Index on MATNR, ERDAT