topic Re: SELECT, performance? in Application Development and Automation Discussions

SELECT, performance?

Former Member — Mon, 30 Jun 2008 14:26:58 GMT

Hi,


CONSTANTS: lv_car(4) VALUE 'CAR%'.

  SELECT * FROM bdcp
           INTO TABLE lt_bdcp
           WHERE CDOBJID LIKE lv_car
           AND   CRETIME =< lv_date.

If table BDCP has more then 700.000 records, is my code ok or do I have to try something else?

Adibo.

Re: SELECT, performance?

Former Member — Mon, 30 Jun 2008 14:28:57 GMT

hi,

Dont Go for SELECT * but give the neccessary fields by name. That will reduce the Processing Time and also the performance will increase.

This will help.

Reward If Helpful.

Regards

Sumit Agarwal

Re: SELECT, performance?

bpawanchand — Mon, 30 Jun 2008 14:29:22 GMT

Always you have to filter while reading of data for better performance and for that sake you can use various techniques to filter data by using select-options and make use of where clause in the select statement..

Regards

Pavan

Re: SELECT, performance?

Former Member — Mon, 30 Jun 2008 14:29:48 GMT

Try to provide more where clause fields in your query.

What are you trying to find using this?

Regards,

Ravi

Re: SELECT, performance?

rthoodi — Mon, 30 Jun 2008 14:31:40 GMT

Don't use Select *, just use require fields for selection, and where is must in order to reduce the time to get data.

Rewards points if useful.

Thanks

Re: SELECT, performance?

Former Member — Mon, 30 Jun 2008 14:34:25 GMT

Hi,

I have to delete some changepointers..


  DATA: lt_bdcp TYPE STANDARD TABLE OF bdcp,
        wa_bdcp TYPE bdcp.
  DATA: lv_time TYPE cpcretime,
        lv_process TYPE process.

  DATA: lv_datum TYPE sy-datum,
        lv_date(14),
        lv_day(2),
        lv_month(2),
        lv_year(4).

  CONSTANTS: lv_car(4) VALUE 'CAR%'. 

  lv_datum = sy-datum.

  lv_year = lv_datum+0(4).
  lv_month = lv_datum+4(2).
  lv_day   = lv_datum+6(2).

  CONCATENATE lv_year lv_month lv_day '235959' INTO lv_date.

  SELECT * FROM bdcp
           INTO TABLE lt_bdcp
           WHERE CDOBJID LIKE lv_car
           AND   CRETIME =< lv_date.
  IF NOT lt_bdcp[] IS INITIAL.

    LOOP AT lt_bdcp INTO wa_bdcp.

      SELECT SINGLE process
             INTO   lv_process
             FROM   bdcps
             WHERE  cpident = wa_bdcp-cpident
             AND    mestype = i_mestyp
             AND    process NE 'X'.
      IF sy-subrc = 0.

        UPDATE bdcps SET    process = 'X'
                     WHERE  cpident = wa_bdcp-cpident
                     AND    mestype = i_mestyp.
      ENDIF.
    ENDLOOP.
  ENDIF.

I will just select bdcp-cpident..This is the field I need

Re: SELECT, performance?

b_deterd2 — Mon, 30 Jun 2008 14:37:28 GMT

Hi,

Check if you can follow an index for table BDCP. Your current statement does NOT follow one of the sap standard indexes .

If you have not created your own index it will result in a poor performance.

regards,

Bert

Re: SELECT, performance?

Former Member — Mon, 30 Jun 2008 14:39:31 GMT


  types: BEGIN OF ls_bdcp,
          cpident type cpident,
         end of ls_bdcp.

  DATA: lt_bdcp TYPE STANDARD TABLE OF ls_bdcp,
        wa_bdcp TYPE ls_bdcp.

  SELECT   cpident
           FROM bdcp
           INTO CORRESPONDING FIELDS OF TABLE lt_bdcp
           WHERE CDOBJID LIKE lv_car
           AND   CRETIME =< lv_date.

Re: SELECT, performance?

Former Member — Mon, 30 Jun 2008 14:49:48 GMT

Hi,

when working with select statement

1. make your selection limit to important fields

2. Use all the primary key fields in where clause

this should definitely help you.

regards

padma

Re: SELECT, performance?

vinod_vemuru2 — Mon, 30 Jun 2008 14:59:15 GMT

Hi Adibo,

Two important things missing in performance point of view.

1. Select only reqired fields(Always avoid Select * unless u r going to use all the selected fields)

2. UR where clause fields are not in the same order as they appear in data base.

3. Avoid using INTO CORRESPONDING FIELDS OF. Instead use INTO TABLE. Use ur internal table as same structure as the field list in select.

Also there is a secondary INDEX available for field CRETIME

So modify ur select like below.

SELECT cpident

FROM bdcp

INTO CORRESPONDING FIELDS OF TABLE lt_bdcp

WHERE CRETIME =< lv_date

AND CDOBJID LIKE lv_car.

Also avoid LIKE in select if possible. This also causes major performance issue.

Got one idea to replace LIKE

RANGES: r_cdobjid FOR bdcp-cdobjid.

CLEAR:r_cdobjid ,r_cdobjid [].

r_cdobjid-sign = 'I'.

r_cdobjid-option = 'CP'.

r_cdobjid-low = 'CAR'.

APPEND r_cdobjid.

SELECT cpident

FROM bdcp

INTO TABLE lt_bdcp1

WHERE CRETIME LE lv_date

AND CDOBJID IN r_cdobjid.

This select will be comparatively faster than first one.

Thanks,

Vinod.

Edited by: Vinod Reddy Vemuru on Jun 30, 2008 8:35 PM

Re: SELECT, performance?

Former Member — Mon, 30 Jun 2008 15:17:33 GMT

Is this code better then first one????


  SELECT   cpident
           FROM bdcp
           INTO CORRESPONDING FIELDS OF TABLE lt_bdcp
           WHERE cdobjid LIKE lv_car
           AND   cretime =< lv_date.
  IF NOT lt_bdcp[] IS INITIAL.

    SELECT cpident
           mestype
           INTO   CORRESPONDING FIELDS OF TABLE lt_bdcps
           FROM   bdcps
           FOR ALL ENTRIES IN lt_bdcp
           WHERE  cpident = lt_bdcp-cpident
           AND    mestype = i_mestyp
           AND    process NE 'X'.

    LOOP AT lt_bdcps INTO wa_bdcps.

      UPDATE bdcps SET    process = 'X'
                   WHERE  cpident = wa_bdcps-cpident
                   AND    mestype = i_mestyp.

    ENDLOOP.

  ENDIF.

Re: SELECT, performance?

former_member251078 — Mon, 30 Jun 2008 16:30:08 GMT

I have look into the following statements based on the performance perspective.

1) SELECT cpident

FROM bdcp

INTO CORRESPONDING FIELDS OF TABLE lt_bdcp

WHERE cdobjid LIKE lv_car

AND cretime =< lv_date.

Rows per Distinct 1 11

generic key values - 10 - 100

Key fields Number of areas that are

1 CDOBJID 312.440 291.368 20.543

2 CRETIME 736.037 717.678 18.025

Please activate the secondary index BDCP-1 to improve the performance.

2) SELECT cpident

mestype

INTO CORRESPONDING FIELDS OF TABLE lt_bdcps

FROM bdcps

FOR ALL ENTRIES IN lt_bdcp

WHERE cpident = lt_bdcp-cpident

AND mestype = i_mestyp

AND process NE 'X'.

Rows per Distinct 1

generic key values - 10

Key fields Number of a

1 CPIDENT 1.025.991 975.746

2 MESTYPE 3.981.314 3.981.314

3 PROCESS 4.089.112 4.089.112

Since you have mentioned NE for process field, oracle could select a full table scan on BDCPS. In this case i would suggest you to include a hint on primary index of BDCPS to improve the performance.

Yours Sincerely

Dileep