topic Re: Problem with BKPF SELECT. in Application Development and Automation Discussions

Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 17:37:20 GMT

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.

Re: Problem with BKPF SELECT.

nabheetscn — Wed, 04 Jan 2012 17:42:17 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 18:38:07 GMT


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

Re: Problem with BKPF SELECT.

nabheetscn — Wed, 04 Jan 2012 18:45:35 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 18:48:38 GMT

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

Rob

Re: Problem with BKPF SELECT.

nabheetscn — Wed, 04 Jan 2012 18:53:20 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 18:59:50 GMT

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

Re: Problem with BKPF SELECT.

nabheetscn — Wed, 04 Jan 2012 19:01:43 GMT

Thanks Rob for the clarifications:)

Nabheet

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 19:43:53 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 19:52:23 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 19:58:55 GMT

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?

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 20:03:24 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 20:05:55 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 20:16:51 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 20:27:07 GMT

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

but before commenting more, lets wait for OP

Re: Problem with BKPF SELECT.

Former Member — Wed, 04 Jan 2012 20:33:59 GMT

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

But yes, let's wait.

Rob

Re: Problem with BKPF SELECT.

sjeevan — Wed, 04 Jan 2012 21:47:22 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Thu, 05 Jan 2012 12:09:02 GMT

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

Re: Problem with BKPF SELECT.

Former Member — Thu, 05 Jan 2012 13:40:07 GMT

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.

Re: Problem with BKPF SELECT.

ThomasZloch — Thu, 05 Jan 2012 13:57:22 GMT

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