FOR ALL ENTRIES problem

[Former Member]

Hi all experts,

I already know the that in most cases inner join is faster than FOR ALL ENTRIES, but here I dont have a choice.

I have to create a report to display period wise cash inflows & outflows. So for the given period am getting all payment documents. My problem is in one payment document many many invoices will get cleared with different posting dates. So am writing another select to get all the data against that payment document using Clearing document no and clearing fiscal year, but it is taking too much time. Any one can give some idea how to modify the following Select queries :

    SELECT * FROM BSAK INTO TABLE IT_BSAK WHERE BUDAT IN S_BUDAT
                                            AND BLART IN R_BLART1
                                            AND BSCHL IN R_BSCHL1.

    SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE AUGBL = IT_BSAK-AUGBL
                                            AND AUGGJ = IT_BSAK-AUGGJ.

ope am clear in my question.

Thanks & Regards,

Rock.

[Former Member]

Hi,

    SELECT * FROM BSAK INTO TABLE IT_BSAK WHERE BUDAT IN S_BUDAT
                                            AND BLART IN R_BLART1
                                            AND BSCHL IN R_BSCHL1.
 
    SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE AUGBL = IT_BSAK-AUGBL
                                            AND AUGGJ = IT_BSAK-AUGGJ.

Check first IT_BSAK[] is initial. Your code will be like

 SELECT * FROM BSAK INTO TABLE IT_BSAK WHERE BUDAT IN S_BUDAT
                                            AND BLART IN R_BLART1
                                            AND BSCHL IN R_BSCHL1.
 if IT_BSAK [] is not initial.
    SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE AUGBL = IT_BSAK-AUGBL
                                            AND AUGGJ = IT_BSAK-AUGGJ.
endif.

For index I have allready said in previous tread check it.

[ravi_lanjewar]

Hi,

trave your program using st05 check which index is using your program.

If Index 1 is using then it take longer time for small amount of data in table then create index on your where condition fields AUGBL and AUGGJ

[Former Member]

Hi Rock,

I would include BUKRS and LIFNR in the conditions of the second select.

SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE LIFNR = IT_BSAK-LIFNR
                                            AND BUKRS = IT_BSAK-BUKRS
                                            AND AUGBL = IT_BSAK-AUGBL
                                            AND AUGGJ = IT_BSAK-AUGGJ.

Regards,

Ana Luisa.

[Rui_Dantas]

Hello,

You first get the clearing documents, and then you want the documents that were cleared by them, right?

Index BSAK~1 has the following fields (it is important you provide the first ones, and as many as possible)

LIFNR

BUKRS

AUGDT

AUGBL

GJAHR

BELNR

BUZEI

At least the first 4 should be the same (because a clearing document has "itself" in the fields AUGBL and AUGDT), so:

SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE LIFNR = IT_BSAK-LIFNR
                                            AND BUKRS = IT_BSAK-BUKRS
                                            AND AUGDT = IT_BSAK-AUGDT
                                            AND AUGBL = IT_BSAK-AUGBL

PS: If you only need the document numbers of the cleared documents this would be even faster, because then BSAK~1 has all the fields you want ("covering index") and the table BSAK is not read.

SELECT LIFNR BUKRS AUGDT AUGBL GJAHR BELNR BUZEI
 FROM BSAK INTO CORRESPONDING FIELDS OF TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE LIFNR = IT_BSAK-LIFNR
                                            AND BUKRS = IT_BSAK-BUKRS
                                            AND AUGDT = IT_BSAK-AUGDT
                                            AND AUGBL = IT_BSAK-AUGBL

Regards, Rui Dantas

Edited by: Rui Pedro Dantas on Sep 27, 2010 10:57 AM

[Former Member]

Hi Rock,

What will you do with your second query, I don't understand it use for??? Instead of write your second query, try to use loop to check your IT_BSAK. I think it will save your time.

Thanks,

[Former Member]

Hi all,thanks for your helpful replies.

Even after adding the above 2 key fields BUKRS & LIFNR performance has hardly improved. I think I have the only option remaining is to add and secondary index.

Hi Thien M. Pham,

in table IT_BSAK I have the payment documents and writing the second select query to get the all cleared documents under those payment documents so that I can understand for which month cleared invoice is raised and can I consider the invoice for the given range of months. The basic intention of this report is to give Project's Period wise Cash Inflows(Payment form Customers) and Cash Outflows(Money paid to Vendors) in a given period of time.

Please let me know anything else can be done?

Hope am clear in my question.

Thanks & Regards,

Rock.

[Rui_Dantas]

>

> Even after adding the above 2 key fields BUKRS & LIFNR performance has hardly improved. I think I have the only option remaining is to add and secondary index.
> Rock.

You shouldn't need an additional secondary index: the index BSAK~1 is the one you need and comes free.

Have you tried with LIFNR + BUKRS + AUGDT + AUGBL as commented above?

If so, have you confirmed in ST05 that index BSAK~1 is used?

Regards, Rui Dantas

[Former Member]

Hi Rui Pedro Dantas,

I have added LIFNR & BUKRS to the above statement & checked ST05, it is accessing BSAK~1 index only. My SELECT query now:

    SELECT * FROM BSAK INTO TABLE IT_BSAK WHERE BUDAT IN S_BUDAT
                                            AND BLART IN R_BLART1
                                            AND BSCHL IN R_BSCHL1.

    SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                              WHERE LIFNR = IT_BSAK-LIFNR
                                                AND BUKRS = IT_BSAK-BUKRS
                                                AND AUGDT = IT_BSAK-AUGDT
                                                AND AUGBL = IT_BSAK-AUGBL.

Am thinking of creating a new index on AUGBL & AUGGJ, will it make any effect?. Anything else I can do to improve the performance?

Thanks & Regards,

Rock.

[Former Member]

Hi Rock,

I have read your requirement again and I have the following comments:

1. Field BELNR in IT_BSAK corresponds to the payment document, therefore in your second select you should look for the documents where AUGBL = IT_BSAK-BELNR.

2. The posting date of the payment is the clearing date of the invoice.

3. A payment document clears itself.

Therefore I would modify the second select as follows:

         SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE LIFNR = IT_BSAK-LIFNR
                                            AND BUKRS = IT_BSAK-BUKRS
                                            AND AUGDT = IT_BSAK-BUDAT
                                            AND AUGBL = IT_BSAK-BELNR
                                            AND BELNR NE IT_BSAK-BELNR.

Regards,

Ana Luisa.

Edited by: Ana-Luisa Sixtos on Sep 27, 2010 2:11 PM

[Rui_Dantas]

Hi. I would need some more details to understand your problem better.

1) You are really using AUGDT in the query, right? You mention you added LIFNR and BUKRS, but in your first you did not have AUGDT.

2) No, AUGBL + AUGGJ is not better than LIFNR + BUKRS + AUGDT + AUGBL, so your index would not improve it.

3) Are you sure that it is the SELECT that is taking time, and not some later operation on the internal tables?

4) What database are you on?

5) How many entries does IT_BSAK have after the first query? How many entries in IT_BSAK1 after the second query?

6) Can you post the index statistics for BSAK_{1? In the explain plan from ST05 click on BSAK}1 and post here all the information.

7) In ST05 you get several lines for this select, right? It is probably grouping several LIFNR + BUKRS + AUGDT + AUGBL in each SELECT with OR's (right?). How many OR's are there?

😎 What times are you getting, and what times do you expect?

RD

[Former Member]

Hi Rui Pedro Dantas,

1) Iam using AUGDT to get the BSAK~1 index and am not bothered about the clearing date, am bothered about the payment documents under give Posting dates range, which am giving in my first select query.

2) Thanks, so I will not create inex for AUGBL & AUGGJ.

3) Yeah it is the most consuming select query, in later operations I have a nested loop(which was also time consuming) which I have already optimized using parallel cursor.

4) Database is Oracle 10g.

5) As no of records depends on the number of periods given in the selection screen, normally 100000 in first select-IT_BSAK and 300000 to 400000 in the second select IT_BSAK.

6) Iam have just started using ST05 (a newbie for ST05 ), I have copied all the data under "Explain" :

SELECT
  *
FROM
  "BSAK"
WHERE
  "MANDT" = :A0 AND "LIFNR" = :A1 AND "BUKRS" = :A2 AND "AUGDT" = :A3 AND "AUGBL" = :A4 OR "MANDT" =
  :A5 AND "LIFNR" = :A6 AND "BUKRS" = :A7 AND "AUGDT" = :A8 AND "AUGBL" = :A9 OR "MANDT" = :A10
  AND "LIFNR" = :A11 AND "BUKRS" = :A12 AND "AUGDT" = :A13 AND "AUGBL" = :A14 OR "MANDT" = :A15 AND
  "LIFNR" = :A16 AND "BUKRS" = :A17 AND "AUGDT" = :A18 AND "AUGBL" = :A19 OR "MANDT" = :A20 AND
  "LIFNR" = :A21 AND "BUKRS" = :A22 AND "AUGDT" = :A23 AND "AUGBL" = :A24


Execution Plan

 SELECT STATEMENT ( Estimated Costs = 8 , Estimated #Rows = 1 )

        3 INLIST ITERATOR

            2 TABLE ACCESS BY INDEX ROWID BSAK
              ( Estim. Costs = 8 , Estim. #Rows = 1 )
              Estim. CPU-Costs = 117,853 Estim. IO-Costs = 8

                1 INDEX RANGE SCAN BSAK~1
                  ( Estim. Costs = 7 , Estim. #Rows = 1 )
                  Search Columns: 5
                  Estim. CPU-Costs = 107,022 Estim. IO-Costs = 7
                  Access Predicates

7) Yeah there are many lines for this Select, the following is the select explanation :

 SELECT
   *
 FROM
   "BSAK"
 WHERE
   "MANDT" = :A0 AND "LIFNR" = :A1 AND "BUKRS" = :A2 AND "AUGDT" = :A3 AND
   "AUGBL" = :A4 OR "MANDT" = :A5 AND "LIFNR" = :A6 AND "BUKRS" = :A7 AND
   "AUGDT" = :A8 AND "AUGBL" = :A9 OR "MANDT" = :A10 AND "LIFNR" = :A11 AND
   "BUKRS" = :A12 AND "AUGDT" = :A13 AND "AUGBL" = :A14 OR "MANDT" = :A15 AND
   "LIFNR" = :A16 AND "BUKRS" = :A17 AND "AUGDT" = :A18 AND "AUGBL" = :A19 OR
   "MANDT" = :A20 AND "LIFNR" = :A21 AND "BUKRS" = :A22 AND "AUGDT" = :A23 AND
   "AUGBL" = :A24

😎 Total execution time for this report for a month is around 4(approx) minutes and generally they will run this report for a quarter i.e for 3 months. I want to optimize as much as possible.

Hope my explanation is clear.

Thanks & Regards,

Rock.

[Rui_Dantas]

Hello Rock,

Thanks for all the detail. Only the answer 6 is not what we needed: in that screen you should click on index BSKA~1, and then post the information that is shown in the popup window.

For now there's one thing you can try: as you see the SELECT's are done 5 documents at a time (SELECT WHERE ... OR .... OR ... OR ... OR ...). You can do it with more documents at a time. Try it with different numbers and compare the results.

For example, for 200 documents at a time, use:

SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE LIFNR = IT_BSAK-LIFNR
                                            AND BUKRS = IT_BSAK-BUKRS
                                            AND AUGDT = IT_BSAK-BUDAT
                                            AND AUGBL = IT_BSAK-BELNR
                                            AND BELNR NE IT_BSAK-BELNR
                                           %_HINTS ORACLE '&MAX_IN_BLOCKING_FACTOR 200&'.

As a result you will see there are less SELECT's in the ST05 trace, but each one is longer. Compare with different values, this will in some cases considerably decrease the runtime.

EDIT: Sorry, a couple of corrections: since this is being translated to OR's (not IN's) you should use MAX_BLOCKING_FACTOR instead of MAX_IN_BLOCKING_FACTOR (or you can use both). Another thing: since the where clause has several fields, 200 is probably longer than Oracle can handled, so try with a smaller number (50 or 100).

SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE LIFNR = IT_BSAK-LIFNR
                                            AND BUKRS = IT_BSAK-BUKRS
                                            AND AUGDT = IT_BSAK-BUDAT
                                            AND AUGBL = IT_BSAK-BELNR
                                            AND BELNR NE IT_BSAK-BELNR
                                           %_HINTS ORACLE '&MAX_BLOCKING_FACTOR 100&'.

Edited by: Rui Pedro Dantas on Sep 28, 2010 11:45 AM

[Former Member]

Hi,

First of all thanks all of you for spending time to reply this thread.

Rui Pedro Dantas,the following is the log you have asked :

Column Name                     #Distinct

MANDT                                          2
LIFNR                                      3,974
BUKRS                                          1
AUGDT                                        386
AUGBL                                     10,190
GJAHR                                          3
BELNR                                     82,104
BUZEI                                         64

Last statistics date                  19.07.2010
Analyze Method                Sample 70,699 Rows
Levels of B-Tree                               2
Number of leaf blocks                      2,344
Number of distinct keys                  150,660
Average leaf blocks per key                    1
Average data blocks per key                    1
Clustering factor                         36,003

I have added MAX_BLOCKING_FACTOR 100 but it doesn't increase any performance.

I have never used MAX_BLOCKING_FACTOR prior to this, will it select 50/100 records in one access to the database? please explain what exactly it does ?any other things which we can write in our select query to improve the performance?

I have one more nested loop which is like :

SORT IT_BSAK1 BY AUGBL AUGGJ.
LOOP AT IT_BSAK INTO WA_BSAK.

IF....
ELSEIF...
...
...

LOOP AT IT_BSAK1 INTO WA_BSAK1 WHERE AUGBL = WA_BSAK-AUGBL
                                           AND AUGGJ = WA_BSAK-AUGGJ
                                           AND "Few other conditions
"Some code here

ENDLOOP.

ELSEIF...
...
...

LOOP AT IT_BSAK1 INTO WA_BSAK1 WHERE AUGBL = WA_BSAK-AUGBL
                                           AND AUGGJ = WA_BSAK-AUGGJ
                                           AND "Few other conditions
"Some code here

ENDLOOP.
....
....
ENDIF.

ENDLOOP.

Anything I can improve in this kind of nested loop?

Thanks & Regards,

Rock.

[Rui_Dantas]

Hi Rock,

1) As you saw in ST05 you have many SELECT's, each one for five documents. The idea of MAX_BLOCKING_FACTOR is that you will have less SELECT's, each one with more documents (more OR's). You can confirm if it worked with a trace in ST05.

2) You should check in SE30 what is really taking time. Be sure to select/create a variant that includes internal table operations, and then sort the result by Net time (descending). Check what percentage you have for each select and for the internal table operations (LOOP's, READ's, etc).

3) That LOOP AT IT_BSAK1 will probably be very time consuming, and you must change it so that it uses a binary search. For that you should now that:

i) If you declare a table as SORTED then READ and LOOP will automatically do the binary search if possible (that is, if you are searching by the first fields in the key)

ii) if you don't declare a table as SORTED you can force it in a READ by using BINARY SEARCH.

iii) but in a LOOP you cannot force a binary search for a non-sorted table.

In your case your table is sorted (you used SORT) but it is not a really SORTED table (it was not declared that way), which means the access is bad. Can't say for sure without seeing the entire code, but from what you have posted it should be enough to change the declarion of IT_BSAK1 to a SORTED TABLE (by AUGBL and AUGGJ). You can then remove the SORT command.

So.. measure in SE30 before and after changing to SORTED table and tell us the results.

Regards,

Rui Dantas

[Former Member]

Hi Rui Pedro Dantas,

I have changed the declaration of IT_BSAK1 as sorted with non unique key AUGBL and AUGGJ and performance of that loop got increased a lot. These fields are not the left most set of columns, will it make any prob?

Thanks Rui Pedro Dantas and others who spared time to reply to this thread and helped me to increase the performance of the program.

Thanks & Regards,

Rock.

[ravi_lanjewar]

Hi,

    SELECT * FROM BSAK INTO TABLE IT_BSAK WHERE BUDAT IN S_BUDAT
                                            AND BLART IN R_BLART1
                                            AND BSCHL IN R_BSCHL1.
 
    SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE AUGBL = IT_BSAK-AUGBL
                                            AND AUGGJ = IT_BSAK-AUGGJ.

Check first IT_BSAK[] is initial. Your code will be like

 SELECT * FROM BSAK INTO TABLE IT_BSAK WHERE BUDAT IN S_BUDAT
                                            AND BLART IN R_BLART1
                                            AND BSCHL IN R_BSCHL1.
 if IT_BSAK [] is not initial.
    SELECT * FROM BSAK INTO TABLE IT_BSAK1 FOR ALL ENTRIES IN IT_BSAK
                                          WHERE AUGBL = IT_BSAK-AUGBL
                                            AND AUGGJ = IT_BSAK-AUGGJ.
endif.

For index I have allready said in previous tread check it.

[Former Member]

Reduce the number of records in FOR ALL ENTRIES to 100 and give test numbers from SQL trace!

How many records does the FOR ALL ENTRIES return, check also SQL trace, i.e. before interface.

How many records do you actually have to process.

[Former Member]

Hi Rock,

Try this out first in your sandbox and see if it works for you.

Create 2 custom secondary indexes on table BSAK.

Custom secondary index 1

budat

blart

bschl

Custom secondary index 2

augbl

auggj

Now try using the following code and see if it helps.

SELECT b~bukrs
       b~lifnr
       b~umsks
       b~umskz
       b~augdt
       b~augbl
       b~zuonr
       b~gjahr
       b~belnr
       b~buzei
  FROM       bsak AS a
  INNER JOIN bsak AS b
  ON  a~augbl EQ b~augbl
  AND a~auggj EQ b~auggj
  WHERE a~budat IN s_budat
  AND   a~blart IN r_blart1
  AND   a~bschl IN r_bschl1.