topic Re: regarding performance Improvements in Application Development and Automation Discussions

regarding performance Improvements

Former Member — Fri, 12 Dec 2008 07:57:37 GMT

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

Re: regarding performance Improvements

Former Member — Fri, 12 Dec 2008 07:59:59 GMT

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

Re: regarding performance Improvements

ThomasZloch — Fri, 12 Dec 2008 08:11:10 GMT

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

Thomas

Re: regarding performance Improvements

Former Member — Fri, 12 Dec 2008 08:53:52 GMT

Hello,

use cursors when it involves lot of database entries.

i have been using it for millions of records.

regards,

prashant

Re: regarding performance Improvements

Former Member — Fri, 12 Dec 2008 09:44:29 GMT

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.

Re: regarding performance Improvements

ThomasZloch — Fri, 12 Dec 2008 12:45:45 GMT

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?

Re: regarding performance Improvements

Former Member — Mon, 15 Dec 2008 13:51:54 GMT

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

Re: regarding performance Improvements

Former Member — Mon, 15 Dec 2008 14:24:08 GMT

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...

Re: regarding performance Improvements

Former Member — Mon, 15 Dec 2008 16:13:06 GMT

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