topic Re: Picking data from a view in Application Development and Automation Discussions

Picking data from a view

Former Member — Fri, 16 Feb 2007 19:01:11 GMT

I am picking up Finance data from a table view COVP using:

select <field list containing 10 fields> into table <itab>....

When i execute this program, the data selection is taking about 15-20 mins or more. (Most of the times it times out!!) How can i fix this? Should i take a join on the two database tables instead of using this view?

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:12:10 GMT

Hi,

Do you require all the data from COEP and COBK tables? If you require then going for this view is OK, otherwise you can write a direct select.

And ofcourse inner joins are little faster compared to views.

Regards

Subramanian

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:16:28 GMT

You're probably not using the index effectively. If you post your code, we might be able to help.

Rob

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:35:12 GMT

Below is my code:

I'm first picking from BKPF then from BSEG and finally for all entires in BSEG, I'm taking a few fields from COVP...

* Pick up the header data as per selection screen from BKPF
  SELECT bukrs belnr gjahr blart budat monat
         tcode bvorg waers kursf hwaer
  FROM   bkpf
  INTO   TABLE it_bkpf
  WHERE  bukrs IN s_bukrs
  AND    gjahr IN s_gjahr
  AND    monat IN s_monat
  AND    tcode IN ('CO88', 'KO88', 'KK87')
  AND    awtyp EQ c_ref_pro.            "'AUAK'

* Pick up the corresponding line items from BSEG
  SELECT bukrs belnr gjahr dmbtr wrbtr ktosl
         zuonr hkont matnr werks menge
  FROM   bseg
  INTO   TABLE it_bseg
  FOR ALL ENTRIES IN it_bkpf
  WHERE  bukrs = it_bkpf-bukrs
  AND    belnr = it_bkpf-belnr
  AND    gjahr = it_bkpf-gjahr
  AND    werks IN s_werks
  AND    ktosl = c_tr_key.              "'PRD'

* Convert field BSEG-ZUONR into 14 char prefixed with 'OR'
  LOOP AT it_bseg INTO wa_bseg.

    CALL FUNCTION 'FI_ALPHA_CONVERT'
      EXPORTING
        i_string = wa_bseg-zuonr
      IMPORTING
        e_string = wa_bseg-zuonr.

    CONCATENATE 'OR' wa_bseg-zuonr+6(12) INTO wa_bseg-zuonr2.

    MODIFY it_bseg FROM wa_bseg TRANSPORTING zuonr2.
  ENDLOOP.

* Select details from COVP for each entry in BSEG
  SELECT perio wtgbtr wogbtr mbgbtr objnr gjahr werks matnr
  FROM   covp
  INTO   CORRESPONDING FIELDS OF
  TABLE it_covp
  FOR ALL ENTRIES IN it_bseg
  WHERE  kokrs = c_cont_ar                                  "'0110'
  AND    perio IN s_monat
  AND    gjahr = it_bseg-gjahr
  AND    werks = it_bseg-werks
  AND    objnr = it_bseg-zuonr2
  AND    wrttp = c_actual.              "'4'

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:41:34 GMT

Ohh... I just realised that all the fields that I'm picking are from a single table, COEP. Shall i use this table rather than the view? But, even this table is very slow...

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:48:37 GMT

Are you sure the problem isn't in the first select from BKPF? It looks to me as if that one isn't using any index, while the one against COVP is using a secondary index.

Rob

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:55:42 GMT

I tried with the COEP table.. It's still equally slow...

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 19:58:34 GMT

In debug, data from BKPF was coming instanly... It's COVP were the debug times out...

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 20:02:34 GMT

Hi,

Do one if check?

data: ws_lines type i.

describe table it_bseg lines ws_lines.

if ws_lines > 0.

select query with for all entries on it_bseg.....

endif.

Regards

Subramanian

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 20:53:29 GMT

That is a good check, but I can see that BSEG does have data... It has 17 lines in my test system... So this won't help my problem...

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 20:57:29 GMT

Is it_bseg-zuonr2 blank in some of the entries? If so, are there many entries in COVP with a blank OBJNR?

Rob

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 21:16:40 GMT

select belnr matnr kstar wkgbtr mbgbtr meinh werks

from coep into table coep_type_tab

where kokrs = c_kokrs

and versn = c_versg

and perio in s_perio

and gjahr in s_gjahr

and matnr in s_matnr

and werks in s_char

and scope = 'PA'

and bukrs = c_bukrs

and kstar in s_kstar.

i would say pass as many from the index combination scope version and the company code are important this reduced the execution time considerabaly for me but even then it does take a lot of time considering the amount of data

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 21:33:06 GMT

None... It's never blank

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 21:36:06 GMT

considering u r getting data from FI -> CO i would pass vrgng as COIN

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 21:46:23 GMT

Try:


* Select details from COVP for each entry in BSEG
SELECT perio wtgbtr wogbtr mbgbtr objnr gjahr werks matnr
FROM   covp
INTO   CORRESPONDING FIELDS OF
TABLE it_covp
FOR ALL ENTRIES IN it_bseg
WHERE  objnr = it_bseg-zuonr2
AND    lednr = '0'
AND    kokrs = c_cont_ar                                    "'0110'
AND    perio IN s_monat
AND    gjahr = it_bseg-gjahr
AND    werks = it_bseg-werks
AND    wrttp = c_actual.

(I added LEDNR = 0).

Rob

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 22:51:54 GMT

That did it... Thanks...

Wow! How did you figure that out?

Re: Picking data from a view

Former Member — Fri, 16 Feb 2007 23:00:03 GMT

By adding the field LEDNR in the selection and equating it to '0' I get instant output.

Re: Picking data from a view

Clemenss — Fri, 16 Feb 2007 23:43:09 GMT

Hi,

just to let everybody know why: There is an index on the database for COEP

Index COEP~1

MANDT

LEDNR

OBJNR

GJAHR

WRTTP

VERSN

KSTAR

HRKFT

PERIO

VRGNG

PAROB

USPOB

VBUND

PARGB

BEKNZ

TWAER

By specifying the (still unused) field lednr the first index key fields LEDNR OBJNR GJAHR give instant access to the required records.

Hint: In SE11 / SE12 it is always a good idea to have a look at the Database Object: After the field list, all indexes including key fields are listed togehter - much much much much much better than awful clicking through index button...

Regards,

Clemens

Re: Picking data from a view

Former Member — Sat, 17 Feb 2007 01:27:15 GMT

Thanks Clemens. That was really helpful information.

Re: Picking data from a view

Former Member — Sun, 18 Feb 2007 20:29:19 GMT

Actually, I was a bit confused to start. I looked at index two which starts with OBJNR. But it turns out this doesn't exist in the database.

So I looked at index one which has OBJNR as the second field but the first field is LEDNR. I'm pretty sure this is a constant. But you should verify this before betting the farm on it. (It's always 0 in our system.)

Rob