SQL-Performance issue 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]

Hi,

your query seems to have been fetching data from the tables:

vbap

marc

tvst

so, I think you can modify your query with the above 3 tables as under:

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 vbap AS b

INNER JOIN marc AS c

ON b_{matnr = c}matnr

AND

b_{werks = c}werks

INNER JOIN tvst AS e

ON b_{vstel = e}vstel

WHERE

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 test the changes how it is working for you.

Please make changes as the per above suggestions and let me know how it worked for you.

Thanks,

Vishnu.

[Former Member]

First thing is avoid joining more than 3 tables. So split your query, use select for all entries construct (with an initial check on the driver table). Select the data roughly in the same order as they appear in the table.

[former_member282823]

Hi,

dont use so many inner joins in the query...may be you can split the query aand try...

Regards,

Ramesh.

[Former Member]

Difficult to say without more information, so run a SQL trace through ST05 to pinpoint where the issue is. At a guess it's table-scanning somewhere.

What database platform are you on? Do you have any custom indexes on VBAP?

[Former Member]

ST05 trace revealed that MARC table was executed in interation when user entered plant on the selection-screen. I split the query such that data from transaction tables and master tables are fetched separately.