SQL- Performance With VBAP table

Former Member — Mon, 17 Mar 2008 19:48:03 GMT

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.

Re: SQL- Performance With VBAP table

Former Member — Mon, 17 Mar 2008 20:12:17 GMT

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

Re: SQL- Performance With VBAP table

Former Member — Mon, 17 Mar 2008 20:51:22 GMT

List of indexes on VBAP

index on MANDT OBJNR

index on MANDT VPMAT & VPWRK

Index on MATNR, ERDAT

topic Re: SQL- Performance With VBAP table in Application Development and Automation Discussions

SQL- Performance With VBAP table

Re: SQL- Performance With VBAP table

Re: SQL- Performance With VBAP table