01-04-2012 5:37 PM
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 bbudat bbelnr bblart wwt_qsshh wqsatz wwt_qbshh wctnumber wwt_acco wwt_withcd lstcd1 lstcd3 lfityp
INTO CORRESPONDING FIELDS OF TABLE itab_rets
FROM with_item AS w INNER JOIN bkpf AS b ON wbelnr = bbelnr
INNER JOIN lfa1 AS l ON wwt_acco = llifnr
WHERE ( bblart = 'OP' OR bblart = '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.
01-05-2012 1:40 PM
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.
01-04-2012 5:42 PM
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
01-04-2012 6:38 PM
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
01-04-2012 6:45 PM
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
01-04-2012 6:48 PM
The order of the fields in the where is irrelevant. This has been shown many times in these forums.
Rob
01-04-2012 6:53 PM
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
01-04-2012 6:59 PM
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
01-04-2012 7:01 PM
01-04-2012 7:43 PM
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
01-04-2012 7:52 PM
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
01-04-2012 7:58 PM
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?
01-04-2012 8:03 PM
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
01-04-2012 8:05 PM
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
01-04-2012 8:16 PM
01-04-2012 8:27 PM
Yes. i have read that. i have some comment specific to this case.
but before commenting more, lets wait for OP
01-04-2012 8:33 PM
I doubt if that will help. There's plenty of other problems here.
But yes, let's wait.
Rob
01-04-2012 9:47 PM
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
01-05-2012 12:09 PM
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
01-05-2012 1:40 PM
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.
01-05-2012 1:57 PM
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
01-05-2012 2:06 PM
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.
01-05-2012 7:45 PM
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!
01-05-2012 8:56 PM
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
01-05-2012 9:34 PM
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
01-05-2012 9:45 PM
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