Select Optimization

[Former Member]

Hi all,

During full installation system taking huge time, when I debugged it, I found that a select statement is taking most of the time.

-

---

T code : EG31

Main Program: SAPLIE01

INCLUDE LIE01U04. "EQUIPMENT_SUCCESSORS

Function Module: EQUIPMENT_SUCCESSORS

this is the code inside the function module.

-

---

standard SAP code----

---

• selection of the newest time-segment of 'reading_date':

• if 'reading_date' ::= ultimo

• only one segment exists

• else

• the newest (last per day) is the one with 'datbi' > 'reading_date'

IF READING_DATE = MAX_DATE.

SELECT * FROM V_EQUI

INTO TABLE EQUI_TAB

WHERE HEQUI = EQUI_NO

AND DATBI = READING_DATE

ORDER BY EQUNR. "*<---

ELSE.

SELECT * FROM V_EQUI

INTO TABLE EQUI_TAB

WHERE HEQUI = EQUI_NO

AND DATAB <= READING_DATE

AND DATBI > READING_DATE

ORDER BY EQUNR. "*<---

ENDIF.

-

---

V_EQUI is a view having 4 tables : EQUI, EQUZ , EQKT , ILOA

I have to check Indexing or what to do ?

please suggest me some solution to solve this issue.

thanks & regards,

Deepak.

[Former Member]

Hi,

You definitely need to check out the indexes used. Also try to specify the primary keys of tables.

Hope this helps u out.

Thanks & Regard

[Former Member]

HI,

Remove the ORDER BY EQUNR from the select statement and write the SORT statment on the table.

or

Check the index on the tables

you can write the seperate select statements on each table...this might help to improve the performence.

[SuhaSaha]

HI,
Remove the ORDER BY EQUNR from the select statement and write the SORT statment on the table.
or 
Check the index on the tables
you can write the seperate select statements on each table...this might help to improve the performence.

Guys,

I never knew we could change SAP code at ease. Plz read the posts thoroughly before replying.

The code which is posted is from a std. FM 'EQUIPMENT_SUCCESSORS'. Now carry on with your posts.

BR,

Suhas

[Former Member]

Hi,

Can any one please give me some idea , how to handle the indexs of the tables of this particular view.

[Former Member]

Hi,

Check this SAP notes for EG31 performance issues. Check if the note is valid for you.

Note 703508 - Bad performance: Installation w/ creation of device location

Note 454635 - Bad performance with installation/removal/replacement

Regards

[Former Member]

Thanks Ravi ,

I saw these notes, the release for these notes are 471 or less than this.

we are currently using ECC 6 , so I think they should be already incorporated , correct me if I am wrong.

regrds

Deepak

[Former Member]

Hi,

Yes you are correct. Just found one more note for the function module

Note 1251386 - Structure list: Runtime problems with subequipment

Check the correction instruction for the select statement

Regards

[Former Member]

Hi,

In this note the statement you mentioned

IF READING_DATE = MAX_DATE.
    SELECT * FROM V_EQUI
      INTO TABLE EQUI_TAB
      WHERE HEQUI = EQUI_NO
      AND   DATBI = READING_DATE

is replaced by

IF reading_date = max_date OR reading_date GE sy-datum.
    SELECT * FROM v_equi
      INTO TABLE equi_tab
      WHERE hequi = equi_no
      AND   datbi = max_date

Regards

[Former Member]

Hi Ravi,

Thats great ,

I feel you already faced this problems.

the note you suggested is implemented but still the performence not improved , is there any other solution ?

thanks & regards

Deepak.

[Former Member]

Hi Deepak,

I have not faced this issue for V_EQUI, but faced similar issues in COPA related tables. The problem was the optimiser picking the wrong index. There was a SAP note to create a different index.

In your note the select query has been changed to pick the correct index.

If you want to check which index your select statement has picked, Use TCODE ST04 (While the Select statement is being executed). Go to the node Session monitor. Your select statement should appear as Active here. Click on it and choose explain. This will give details of the index picked.

You can check if proper index has indeed been picked after applying the note.

Regards,

[Former Member]

Hi All, I know this response is a lot late- but I looked this post up when we faced the exact same issue and this fix for this from our end was the Databse hint related to SAP Note:  456066 (Access to EQUZ ~ H) and reason is:

"The equipment used in IS-U/CCS (EqTyp device) does not have any links to other equipment via field EQUZ-HEQUI. For this reason, the selectivity of this index is very bad, and the Cost Based Optimizer may select an inappropriate access path".

[Former Member]

Well, there is an update to this as well. The problem was not resolved with just the Note: 456066 being implemented; we had to follow that one up with Note: 494471 which was Modification M6 as per the Cookbook. -> Switching off Hierarchy checks during Installation of Equipment. That did it and things got moving after a long standstill.