Problem with BKPF SELECT.

[Former Member]

Hi everyone! Quick question. I'm using a select statement from table BKPF with joins to WITH_ITEM and LFA1. The thing is that it takes too long to execute, and finally i get a dump timeout. i tried to replace BKPF for BSIK or BSAK in case that helped, but to no avail.

Here's the select code, perhaps there's something I'm not noticing.

Thanks so much for taking the time to look at it.

Have a good one!

FORM get_docs TABLES itab_rets STRUCTURE itab_ret.

SELECT b_{budat b}belnr b_{blart w}wt_qsshh w_{qsatz w}wt_qbshh w_{ctnumber w}wt_acco w_{wt_withcd l}stcd1 l_{stcd3 l}fityp

INTO CORRESPONDING FIELDS OF TABLE itab_rets

FROM with_item AS w INNER JOIN bkpf AS b ON w_{belnr = b}belnr

INNER JOIN lfa1 AS l ON w_{wt_acco = l}lifnr

WHERE ( b_{blart = 'OP' OR b}blart = 'OT' OR b~blart = 'OE' )

AND w~witht = '15'

AND b~blart IN s_blart

AND b~budat IN s_budat

AND b~belnr IN s_belnr

AND b~gjahr IN s_gjahr

AND b~bukrs EQ s_bukrs

AND b~bldat IN s_bldat

AND w~wt_qsshh <> 0

AND w~wt_qbshh <> 0.

[nabheetscn]

First of all what you can do is you need to revise the where fields order in the where clause... Try using for all entries make sure you have used correct order of fields in where clause as it will hit the primary index and perform faster...

Nabheet

[Former Member]

First of all what you can do is you need to revise the where fields order in the where clause... Try using for all entries make sure you have used correct order of fields in where clause as it will hit the primary index and perform faster...
> 
> Nabheet

Absolutely incorrect.

Rob

[nabheetscn]

Hi Rob

I think breaking the query into independent selects and using for all entries along with primary index if possible will be a better solution. Please let us know if we can correct it in some other way ..

Thanks

Nabheet

[Former Member]

The order of the fields in the where is irrelevant. This has been shown many times in these forums.

Rob

[nabheetscn]

Sir It was concerning bkpf if we use in where clause the fields as bukrs belnr gjhar it will then refer to primary index ...is this understanding incorrect..?

Nabheet

[Former Member]

It's not necessarily correct. THhe OP is using an IN operator on these fields, so if they are empty, the index will likely not be used. But you said:

used correct order of fields in where

The orde of the fields in the where clause doesn't matter.

A better answer to the question would be to suggest to the OP to run a trace (ST05) and see what index (probably none) was actually used.

Rob

[nabheetscn]

Thanks Rob for the clarifications:)

Nabheet

[Former Member]

ok lets concentrate on romero's problem. shall we we had enough discussion on this in SDN. nabheet, just scroll though them when you are free.. i know you are a busy guy...

romeo,

i would suggest to break down the query,

1. select from BKPF, here keep the key field(combination) only in the where clause and take out the nonkey fields

after the select statement(into table lt_bkpf) , use delete lt_bkpf where <non key field> NE s_<fields>(what ever u have)

2. select from with_item with a for all entries in lt_bkpf only with belnr(or add key fields if you have any, i am not infront of sap write now). again follow the delete for non key fields as described in step 1.

and what is this?? avoid writing these

> AND w~wt_qsshh <> 0
> AND w~wt_qbshh <> 0.

3. selecting entries from LFA1.

just copy lt_withit to another internal table lt_withit_temp. sort lt_withit_temp by wt_acco. then delete adjacent duplicates from lt_withit_temp comparing wt_acco. so you have unique wt_acco values now

now hit LFA1 with for all entries in lt_withit_temp where lifnr = lt_withit_temp-wt_acco

try these and let me know

[Former Member]

Nope - that's wrong too

Given that the OP hasn't given enough information (the trace) to give a good answer, I would only suggest trying:

SELECT b~budat b~belnr b~blart w~wt_qsshh w~qsatz
       w~wt_qbshh w~ctnumber w~wt_acco w~wt_withcd
       l~stcd1 l~stcd3 l~fityp

INTO CORRESPONDING FIELDS OF TABLE itab_rets

FROM with_item AS w
  INNER JOIN bkpf AS b ON w~belnr = b~belnr
  INNER JOIN lfa1 AS l ON w~wt_acco = l~lifnr
  WHERE ( b~blart = 'OP' OR b~blart = 'OT' OR b~blart = 'OE' )

  AND w~witht = '15'
  AND b~blart IN s_blart
  AND b~budat IN s_budat
  AND b~belnr IN s_belnr
  AND b~gjahr IN s_gjahr
  AND b~bukrs EQ s_bukrs
  AND b~bldat IN s_bldat
  AND w~wt_qsshh <> 0
  AND w~wt_qbshh <> 0

  AND b~bstat = space.   <== Add

I should have added that because of the select-options that could be empty, I don't think this will help much.

Rob

Edited by: Rob Burbank on Jan 4, 2012 2:55 PM

[Former Member]

Rob, you are the man. i am not doubting what you said but clarify me on one thing

FROM with_item AS w
  INNER JOIN bkpf AS b ON w~belnr = b~belnr
  INNER JOIN lfa1 AS l ON w~wt_acco = l~lifnr

this means first get the data from W and B, get the combination with you and then hit L .. right? being non distinct, repetitive keys for LFA1, how is this helping performance?

and what is wrong in breaking down the query?

[Former Member]

My point was that properly constructed joins are faster than for all entries and that <> in the where (if used correctly) will not hurt.

Rob

[Former Member]

agree again, but lets consider a worst case scenario.. ALL the select options are blank :P, now what!! the join will hurt or not?

ya so whats fed into where clause really does matter here... So romero, save us from this

[Former Member]

Have a look at [JOINS vs. FOR ALL ENTRIES - Which Performs Better?|]. If all select-options were empty I would expect for all entries could be much slower.

Rob

[Former Member]

Yes. i have read that. i have some comment specific to this case.

but before commenting more, lets wait for OP

[Former Member]

I doubt if that will help. There's plenty of other problems here.

But yes, let's wait.

Rob

[sjeevan]

There are 1.8 million entries in BKPF in our system. So, it's a huge table. If you're running your report wide open without any selection options entries in selection screen in any primary key of any of the three tables, it's gonna give you a timeout.

Run the program with the same input parameters which is giving time out and run SM50 in another session , it will show which table is being read during program run, using that you can figure out which table is taking too long, from that information you'd have to put more restriction in the where condition (on preferably primary keys)

Edited by: Jeevan Sagar on Jan 4, 2012 4:47 PM

[Former Member]

Hi,

First get records from BKPF table and put one internal table. based on this internal table, get records from WITH_ITEM tables and then LFA1.

Use theree SELECT statements instead of INNER JOIN statement.

Hope this one is helps for you

[former_member184681]

Hi,

To my point of view, separate SELECT statements will be quicker than one with JOINs. Joining three tables that are as large as these must take that much time. Also, keep the WHERE conditions in the primary key where possible, starting with the leftmost fields - this will significantly limit the number of entries at the beginning of the processing. Moreover, INTO TABLE works faster than INTO CORRESPONDING FIELDS OF TABLE (but note that you have to define local types accordingly). All this is described in TAW* & BC* SAP training courses saying about DB operations performance.

Try the following code:

SELECT budat belnr blart
  FROM bkpf
  INTO TABLE it_bkpf
  WHERE bukrs IN s_bukrs AND
	belnr IN s_belnr AND
	gjahr IN s_gjahr AND
	blart IN s_blart AND
	bldat IN s_bldat AND
	budat IN s_budat.

IF it_bkpf[] IS NOT INITIAL.
  SELECT wt_qsshh qsatz wt_qbshh ctnumber wt_acco wt_withcd
    FROM with_item
    INTO TABLE it_with_item
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs = it_bkpf-bukrs AND
          belnr = it_bkpf-belnr AND
          gjahr = it_bkpf-gjahr AND
	  wt_qsshh = 0 AND
	  wt_wbshh = 0.

  IF it_with_item[] IS NOT INITIAL.
    SELECT stcd1 stcd3 fityp
      FROM lfa1
      INTO TABLE it_lfa1
      FOR ALL ENTRIES IN it_with_item
      WHERE lifnr = it_with_item-wt_acco.
  ENDIF.
ENDIF.

[ThomasZloch]

I would suggest trying to add BUKRS (for performance) and GJAHR (for correct results) to the ON-conditions between WITH_ITEM and BKPF first, as well as the BSTAT addition to the WHERE-clause as suggested by Rob, and only if that doesn't help, twist the code around altogether based on other suggestions in this thread.

Joins work very well when constructed properly, the CBO can decide at runtime about the best access path (e.g. based on filled selection criteria), rather than the developer trying to be smarter by hard-coding a fixed path via FAE-contructs.

Thomas

[Former Member]

Hi,

You have to concentrate on order of tables the way you are joining, in that oder only you have to fetch data from tables and in where condition also incorrect order.

[Former Member]

Thanks everyone for the comments, code and suggestions! I've struggled with this issue all day yesterday and here´s what I came up with. I'm not sure performance wise, though, but it's working alright.

=====

FORM get_docs TABLES itab_rets STRUCTURE itab_ret.

  TYPES: BEGIN OF t_bkpf,
          bukrs LIKE bkpf-bukrs,
          budat LIKE bkpf-budat,
          belnr LIKE bkpf-belnr,
          blart LIKE bkpf-blart,
    END OF t_bkpf.

  TYPES: BEGIN OF t_with_item,
    belnr     LIKE with_item-belnr,
    wt_qsshh  LIKE with_item-wt_qsshh,
    qsatz     LIKE with_item-qsatz,
    wt_qbshh  LIKE with_item-wt_qbshh,
    ctnumber  LIKE with_item-ctnumber,
    wt_acco   LIKE with_item-wt_acco,
  END OF t_with_item.

  TYPES: BEGIN OF t_lfa1,
          lifnr LIKE lfa1-lifnr,
          stcd1 LIKE lfa1-stcd1,
          stcd3 LIKE lfa1-stcd3,
          fityp LIKE lfa1-fityp,
    END OF t_lfa1.

DATA: it_bkpf      TYPE t_bkpf      OCCURS 0 WITH HEADER LINE.
DATA: it_with_item TYPE t_with_item OCCURS 0 WITH HEADER LINE.
DATA: it_lfa1      TYPE t_lfa1      OCCURS 0 WITH HEADER LINE.

DATA: wa_ret            TYPE t_documento,
           wa_bkpf          TYPE t_bkpf,
           wa_with_item  TYPE t_with_item,
           wa_lfa1           TYPE t_lfa1.

  DATA: d_relac_ox.
  DATA: jur_cert.

SELECT bukrs budat belnr blart
  FROM bkpf
  INTO CORRESPONDING FIELDS OF TABLE it_bkpf
  WHERE bukrs EQ s_bukrs AND
  belnr IN s_belnr AND
  blart IN s_blart AND
  bldat IN s_bldat AND
  budat IN s_budat.

IF it_bkpf[] IS NOT INITIAL.
  SELECT belnr wt_qsshh qsatz wt_qbshh ctnumber wt_acco" wt_withcd
    FROM with_item
    INTO CORRESPONDING FIELDS OF TABLE it_with_item
    FOR ALL ENTRIES IN it_bkpf
    WHERE bukrs = it_bkpf-bukrs AND
          belnr = it_bkpf-belnr AND
          witht = '15' AND
          wt_withcd = '02' AND
          wt_qsshh <> 0 AND
          wt_qbshh <> 0.

  IF it_with_item[] IS NOT INITIAL.
    SELECT lifnr stcd1 stcd3 fityp
      FROM lfa1
      INTO CORRESPONDING FIELDS OF TABLE it_lfa1
      FOR ALL ENTRIES IN  it_with_item
      WHERE lifnr =  it_with_item-wt_acco.
  ENDIF.
ENDIF.

  LOOP AT it_bkpf INTO wa_bkpf.
    wa_ret-budat = wa_bkpf-budat.
    wa_ret-belnr = wa_bkpf-belnr.
    wa_ret-blart = wa_bkpf-blart.

    READ TABLE it_with_item into wa_with_item WITH KEY belnr = wa_bkpf-belnr.
    wa_ret-wt_qsshh    = wa_with_item-wt_qsshh.
    wa_ret-qsatz       = wa_with_item-qsatz.
    wa_ret-wt_qbshh    = wa_with_item-wt_qbshh.
    wa_ret-ctnumber    = wa_with_item-ctnumber.
    wa_ret-wt_acco     = wa_with_item-wt_acco.

    ENDLOOP.

    READ TABLE it_lfa1 into wa_lfa1 WITH KEY lifnr = wa_with_item-wt_acco.
    wa_ret-stcd1 = wa_lfa1-stcd1.
    wa_ret-stcd3 = wa_lfa1-stcd3.
    wa_ret-fityp = wa_lfa1-fityp.

    APPEND wa_ret TO itab_rets.


ENDFORM.

Thanks again everyone, I tried to gather something from all answer and learned a lot just from reading your opinions!

[Former Member]

I'm not sure performance wise, though, but it's working alright.!

Well, it was a performance question, so please let us know how it works out. Be sure to test with lots of different entries in the select-options. And leave some of them empty like the users will do.

Rob

[ThomasZloch]

There we have it again, a perfect example. First you are splitting the select into three separate parts that cannot be optimized together, only to combine the results again into one final internal table using a LOOP and READ TABLE construct. With a proper join-statement featuring correct (means index-utilizing) ON- and WHERE-conditions all this could be done with 20% of the code and would probably run faster as well.

It's up to you.

Thomas

[Former Member]

Since the LOOP includes READs that are on standard internal tables without the BINARY SEARCH addition, I expect it to run much more slowly.

JOINS are admittedly harder to construct, but are generally faster. See the blog I mentioned earlier.

Rob