topic Re: Query on table EABL & EABLG taking long in Application Development and Automation Discussions

Query on table EABL & EABLG taking long

vikrant_guptarya — Thu, 14 Jul 2011 11:08:20 GMT

Hi Experts,

We have this code running for almost 4 years now. suddenly this piece of code has started taking 10 times more time.

I believe no increase in data in these tables could have increased the access time by 10 times in the last 4 days. We have also ran the stats for the table EABLG & EABL, but still the performance has not improved.

Please let me know if there is any remedy for this or should i raise a OSS message for this.

SELECT e~anlage

e~ablesgr

a~ablstat

e~adatsoll

FROM eablg AS e INNER JOIN

eabl AS a

ON e_{ablbelnr = a}ablbelnr

INTO TABLE it_eabl_eablg

WHERE a~ablstat EQ '0'

AND e~ablesgr EQ '06'

AND e~anlage IN s_anlage.

IF it_eabl_eablg[] IS NOT INITIAL.

SELECT e1~anlage

e1~ablesgr

a1~ablstat

e1~adatsoll

FROM eablg AS e1 INNER JOIN

eabl AS a1

ON e1_{ablbelnr = a1}ablbelnr

INTO TABLE it_eabl_eablg1

FOR ALL ENTRIES IN it_eabl_eablg

WHERE e1~anlage = it_eabl_eablg-anlage

AND e1~ablesgr EQ '03'.

Re: Query on table EABL & EABLG taking long

Former Member — Thu, 14 Jul 2011 14:54:46 GMT

If this is not SAP code, I wouldn't bother raising an OSS message.

Rob

Re: Query on table EABL & EABLG taking long

Former Member — Thu, 14 Jul 2011 15:56:52 GMT

I would suggest to re-run the brconnect(table stats) , as the reason seems to be select query utilizing the wrong index .

Regards

Pawan

Re: Query on table EABL & EABLG taking long

Former Member — Thu, 14 Jul 2011 16:11:44 GMT

I don't think it's using the wrong index. I don't think there is any index it can use.

Rob

Re: Query on table EABL & EABLG taking long

volker_borowski2 — Fri, 15 Jul 2011 07:25:05 GMT

Hi,

you need to check the DB execution plan. Compare the plan to QA system.

I assume due to some minor stats canges, your join order or the join method changed which might result in a big difference.

If the plan in QA is different than in PRD, post both.

if the plan of QA is better, export the table stats from QA to PRD for both tables

(sap note available for that) and lock the stats on those tables in PRD after that action.

Volker

Re: Query on table EABL & EABLG taking long

vikrant_guptarya — Sat, 16 Jul 2011 00:19:40 GMT

Thanks guys.. Seems the stats update kicked in only after a day