Performance Issue in a Query

[Former Member]

Hi,

I have written a query,

SELECT VBAK~VKORG VBAK~VBTYP VBAK~AUART VBAK~VTWEG VBAK~SPART
             VBAK~KUNNR VBAK~KVGR1 VBAK~KVGR3 VBAP~WERKS VBAP~MATNR
             VBAP~MVGR1 VBAP~MVGR2 VBAP~MVGR3 VBAP~MVGR3 VBAP~MVGR4
             VBAP~MVGR5 VBEP~VBELN VBEP~POSNR VBEP~ETENR VBEP~MBDAT
             VBEP~WADAT
             VBAP~KDMAT VBAP~ERDAT VBAP~ERZET VBAP~KWMENG
             INTO CORRESPONDING FIELDS OF
             TABLE INT_OPEN_SCH_LINES
             FROM VBAK
             INNER JOIN VBAP ON VBAK~VBELN = VBAP~VBELN
             INNER JOIN VBEP ON VBEP~POSNR = VBAP~POSNR AND
                                VBEP~VBELN = VBAK~VBELN AND
                                VBEP~VBELN = VBAP~VBELN
             FOR ALL ENTRIES IN T_TVEP
             WHERE VBAP~WERKS IN S_WERKS   AND
                   VBAP~MATNR IN S_MATNR   AND
                   VBAP~MATKL IN S_MATKL   AND
                   VBAP~ABGRU IN S_ABGRU   AND
                   VBAK~KUNNR IN S_KUNNR   AND
                   VBEP~ETENR = C_0001 AND
                   VBEP~WADAT = D0         AND
                   VBAK~VBTYP IN S_VBTYP   AND
                   VBAK~AUART IN S_AUART   AND
                   VBEP~ETTYP = T_TVEP-ETTYP AND
                   VBAK~KVGR3 IN S_KVGR3.

This is working fine in Development and Production systems and takes just 2 sec to execute. But the code takes more than 5 min to execute in Quality system. What could be the problem? The query cannot be modified, as this is the requirement.

Please help me out.

Thanks,

DhanaLakshmi M S

[mvoros]

Hi,

you can try to run this report with SQL trace on and compare results in DEV and QAS. Maybe it will give you some clues about the performance problem in QAS.

https://help.sap.com/saphelp_nw04/helpdata/en/75/97ea08f4832c4b9feb80aa653f0d26/frameset.htm

Cheers

[GauthamV]

check the data from the tables that is entering into the select statement

in debugging mode in QAS.

If it is working fine in PRD then it might be only problem with data in QAS.

[Former Member]

Hi

Please remove the clause INTO CORRESPONDING FIELDS OF TABLE INT_OPEN_SCH_LINES.

Instead, use INTO TABLE INT_OPEN_SCH_LINES only and make sure the fields fetched are in order same as the once defined in the internal table INT_OPEN_SCH_LINES.

This will surely help.

[Former Member]

Hi,

Please remove the 'into corresponding fields of table' in your query.

One more important thing to remember is that when you are using for all entries is that.

if t_tvep is not initial.

write your select query here.

endif.

I hope this will solve your problem

[ThomasZloch]

Are you sure that the exact same selection criteria was used?

If yes, try:

- removing the second ON-condition in the VBEP join, as it is not necessary and might throw off the optimizer

- comparing the SQL trace in QA and PRD and see where the difference is, assuming that these two systems hold about the same amount of data

- updating the database statistics in QA for the involved tables (I think via tx DB20)

Thomas

[Former Member]

Moved to ABAP Performance forum...

[christine_evans]

It could be that the table stats are not up-to-date in QA so making the database optimiser use the wrong index. Check in ST05 SQL trace. Personally, so long as something is working properly in Production I wouldn't get really worried about it being a bit slow in QA.

[Former Member]

run the SQL Trace in all systems,

see here

SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

It will tell you:

+ the time

+ the number of records coming back

+ use explain, some databases should you the size of the tables (can be different)

+ all will show you the execution plan, order of processing and indexes etc.

+ check details what is the actual database statement, which INs are used, which not.

Of course don't forget, run a few times, before you trace, and trace 2 times each, is the result the

same?

Siegfried

[Former Member]

Hi Dhanalakshmi,

Avoid 'into corresponding fields of ' insted use 'into' only and take care of order of the fields.

Whenever we use 'For all Entries' writhe the following mandatory condition.

if not t_tvep[] is initial.

write Select query.

endif.

I think This will solve ur problem.

Regards,

Sri Ramya G

[former_member251078]

Hi,

As Christine Evans mentioned mostly likely this could be the issue. Check out in quality system the statistics are upto date or not. If the statistics are not upto date, optimizer will choose the wrong index if there is no statistics mostly likely could be full table scans in the tables included in the where clause. which will take longer time. Which database you are using ?? if you are using oracle 10g, compare the following database parameter in prod and qa. make necessary changes for these parameters if needed.

Refer SAP Note 830576.

OPTIMIZER_INDEX_CACHING

OPTIMIZER_INDEX_COST_ADJ

INDEXJOIN_ENABLED

Hope this helps,

Regards

Dileep