topic Re: Performance issue with a Select statement? in Application Development and Automation Discussions

Performance issue with a Select statement?

Former Member — Thu, 28 Jun 2007 07:18:12 GMT

Hi experts,

I am facing a problem where a particular select from CAUFV view is taking nearly 1 and half hours .

SELECT AUFNR AUART AUTYP PLNBEZ WERKS RSNUM GAMNG GMEIN

GSTRP GLTRP OBJNR SFCPF DISPO FEVOR FTRMS IGMNG

APPENDING CORRESPONDING FIELDS OF TABLE I_TAB

FROM CAUFV

WHERE AUFNR IN PA_AUFNR

AND PLNBEZ IN PA_MATNR

AND AUART IN PA_AUART

AND WERKS = PA_WERKS

AND DISPO IN PA_DISPO

AND FEVOR IN PA_FEVOR

AND GSTRP IN PA_GSTRP

AND GLTRP IN PA_GLTRP

AND FTRMS IN PA_FTRMS

AND LOEKZ = SPACE.

In the selection screen none of the above mentioned parameters are entered.So maintaining indexes also wont help.

Kindly suggest if ther is sme other way of improving the performance .

regards,

Ashwin.

Re: Performance issue with a Select statement?

Former Member — Thu, 28 Jun 2007 07:20:28 GMT

1st arrenge the order of field in internal table same as in order u r fetch from database table

SELECT AUFNR AUART AUTYP PLNBEZ WERKS RSNUM GAMNG GMEIN
GSTRP GLTRP OBJNR SFCPF DISPO FEVOR FTRMS IGMNG

FROM CAUFV into table I_TAB
WHERE AUFNR IN PA_AUFNR
AND PLNBEZ IN PA_MATNR
AND AUART IN PA_AUART
AND WERKS = PA_WERKS
AND DISPO IN PA_DISPO
AND FEVOR IN PA_FEVOR
AND GSTRP IN PA_GSTRP
AND GLTRP IN PA_GLTRP
AND FTRMS IN PA_FTRMS
AND LOEKZ = SPACE.

kishan negi

Re: Performance issue with a Select statement?

Former Member — Thu, 28 Jun 2007 07:25:07 GMT

If the selections are parameters, then you need to Equate.

Also, atleast make the key fields mandatory. So that, you can get the data based on those fields, and then apply other filetrations based on inputs.

Regards

Raj

Re: Performance issue with a Select statement?

Former Member — Thu, 28 Jun 2007 07:29:59 GMT

Hi,

Try to avoid APPENDING, CORRESPONDING FIELDS and the where condition should be as per the Index according to the base table u r using,

Try to use primary key in where condition

Create internal table as per the Field selection remove Correspondin fields

Regards,

Nandha

Reward if it helps

Re: Performance issue with a Select statement?

former_member491305 — Thu, 28 Jun 2007 07:30:39 GMT

Hi,

I know that CAUFV is a view.But try changing the order of the fields in the where condition according to the fields ordered in that view.

For Eg: You can put AUART next to AUFNR in where condition.

Re: Performance issue with a Select statement?

Former Member — Sun, 01 Jul 2007 08:23:04 GMT

Ashwin,

If performance is still an issue, you can try creating a materialized view.

Thanks

Ganesh.S

Re: Performance issue with a Select statement?

Former Member — Mon, 02 Jul 2007 11:15:11 GMT

Ganesh,

Thanks for your inputs.

Could you please provide me with some more details for what exactly you mean by a materialized view.

regards,

Ashwin

Re: Performance issue with a Select statement?

Former Member — Wed, 04 Jul 2007 20:03:55 GMT

Hey Ashwin,

I am trying to get one created with Basis help as I do no have direct access to DB.

If we can get that done, it will be awesome. I will keep you posted.

Thanks

Ganesh.S

Re: Performance issue with a Select statement?

Former Member — Wed, 04 Jul 2007 20:46:36 GMT

This is counter-intuitive, but so long as you are selecting on a single value of WERKS, try:



RANGES: r_autyp FOR caufv-autyp.
DATA  : d1 LIKE dd07l-domvalue_l,
        d2 LIKE dd07l-domvalue_h.

r_autyp-sign   = 'I'.
SELECT domvalue_l domvalue_h
  FROM  dd07l
  INTO  (d1, d2)
  WHERE domname  = 'AUFTYP'
  AND   as4local = 'A'.
  IF d2 IS INITIAL.
    r_autyp-option = 'EQ'.
    r_autyp-low    = d1.
    CLEAR r_autyp-high.
  ELSE.
    r_autyp-option = 'BT'.
    r_autyp-low    = d1.
    r_autyp-high   = d2.
  ENDIF.
  APPEND r_autyp.
ENDSELECT.

SELECT aufnr auart autyp plnbez werks rsnum gamng gmein
        gstrp gltrp objnr sfcpf dispo fevor ftrms igmng
        APPENDING CORRESPONDING FIELDS OF TABLE i_tab
  FROM caufv
  WHERE aufnr  IN pa_aufnr
    AND plnbez IN pa_matnr
    AND auart  IN pa_auart
    AND autyp  IN r_autyp   <======== Forces use of index
    AND werks   = pa_werks
    AND dispo  IN pa_dispo
    AND fevor  IN pa_fevor
    AND gstrp  IN pa_gstrp
    AND gltrp  IN pa_gltrp
    AND ftrms  IN pa_ftrms
    AND loekz   = space.

Index AUFK~B is on AUTYP and WERKS. By selecting all possible values of AUTYP from the domain and using that in the select, it can forse the use of this index and should cut execution time in about half; not great, but it's a start.

Rob

Message was edited by:

Rob Burbank