regarding performance Improvements

[Former Member]

Hi,

I have a situation .

We are fetching data from EQUI and EQBS tables.

While fetching the program taking long time to run ( some times time out).

Can some body suggest if any body of you got this issue before.

Any kind of help is valuable for us.

Thanks in Advance

Gaurav

[Former Member]

Hi Gaurav,

Go through this link.

[https://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1408d5f234]

Regards,

Raj

[ThomasZloch]

You should tell us the problematic select statement in case you expect any help.

Thomas

[Former Member]

Hello,

use cursors when it involves lot of database entries.

i have been using it for millions of records.

regards,

prashant

[Former Member]

thnks for any inputs.

We have used the cursors and the packet splitting as well still we are not getting any help from the code written there.

we used indexes as well but problem is not solved.

please go through the code below.

  OPEN CURSOR l_cur1 FOR
       SELECT equnr matnr charge sernr
             FROM equi
             FOR ALL ENTRIES IN i_output
             WHERE matnr  EQ i_output-matnr
             AND   charge EQ i_output-charg
             AND   sernr  IN s_sernr.
   DO.
    FETCH NEXT CURSOR l_cur1 APPENDING CORRESPONDING FIELDS OF TABLE i_eqbs  PACKAGE SIZE 10000.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.
  ENDDO.

  CLOSE CURSOR l_cur1.

reply if you got any thing .

Regards,

gaurav

Edited by: Matt on Dec 12, 2008 1:49 PM - Edited by moderator to make it readable.

[ThomasZloch]

This should be supported by index EQUI_{B40 (MATNR, S_EQBS, SERNR) or maybe EQUI}A (SERNR, MATNR). However if equi is large and i_output is empty or contains many entries and/or s_sernr is blank, then there is nothing you can do about a long runtime. You have to narrow down the selection if you want a quick result.

In don't think the cursor concept will be of any help here.

Thomas

P.S. where is the select on EQBS?

[Former Member]

Hi,

there is NO difference in using an implicit cursor (SELECT ) and an explicit one (OPEN)

in terms of performance.

The implicit cursor also opens one - but behind the scenes.

If you want to separate the OPEN part from the fetch part you would go for explicit definition.

There is also the possibility of reusing it as long you not closed it. An implicit cursor is closed after the last fetched record.

In 95% percent of the performance issues a flawed SQL statement is the performanced killer.

• check the FAE table if you can replace it by joining the 2 tables on a common key that is supported by an index on both tables.

• Have an EFFICIENT WHERE filter in place to reduce the amount of data to rtrieve.

bye

yk

[Former Member]

Hi,

Dont use joins, rather use for all entries.

When using for all entries do remenber to select all the key fields weather you are using or not.

Thanks,

krishna...

[Former Member]

Hi,

don't want to move you in one direction but FAE vs. Joins are discussed a lot in SDN

(Joins turned out mostly as the best alternative)

Look at the discussions:

https://forums.sdn.sap.com/click.jspa?searchID=-1&messageID=6630311

https://forums.sdn.sap.com/click.jspa?searchID=-1&messageID=6607176

https://forums.sdn.sap.com/click.jspa?searchID=-1&messageID=6590236

and read

SAP Note 48230 - Parameters for the SELECT ... FOR ALL ENTRIES statement

Always question the the recommendations if there is no really proof or references by the authors.

Bye

yk