SAP BRF+ nested loops with DBLookups performance issue

[prince_joshi]

Hi Experts,

We have a requirement to create BRF+ application for certain rules in our organization.

I need to fetch multiple records from Billing header table(DFKKINVBILL_H) , then fetch corresponding Billing line item details(DFKKINVBILL_I)(using key BILLDOCNO) and then fetch the corresponding provider contract details(DFKK_VT_I)(using key VTREF). At this level I need to validate some of the rules for each bill document details (Bill header & it⁢em) and provider contract details.

I created a function with ruleset where I was using DBlookup for DFKKINVBILL_H to fetch all bill doc headers & then for each bill doc header I am using NESTED LOOPs with DBlookup for DFKKINVBILL_I and DFKK_VT_I.

As there are 3 DBlookups and 2 nested loops and I don't think that is a good idea as DBlookup of DFKKINVBILL_I and DFKK_VT_I will hit database for each bill document. This will definitely cause performance issues.

It is same as SELECT statement inside loop which is not at all a good approach.

Example: Nested loops and multiple DBlookup in my case

Function: Zvalidate_data

• First DBlookup DFKKINVBILL_H

Loop 1 starts for above DFKKINVBILL_H.

Second DBlookup DFKKINVBILL_I comparing billdocno

Loop 2 starts for above DFKKINVBILL_I

Third DBlookup DFKK_VT_I. comparing vtref

=> "PERFORMING RULE VALIDATION HERE" <=

Loop 2 ends.

Loop 1 ends.

Could any of you suggest what could be the best solution from performance perspective. Don't we have something like inner join/for all entries or any other way to achieve it?

Please note: I don't want to collect above table data in ABAP layer because we wanted these rules should be independent of ABAP and in future if business wants to fetch the table data using some other WHERE condition then they can directly add in BRF+ instead of ABAP. We are looking for a rule engine which is independent of coding. So, rules can be maintained in BRF+ by business people easily.

Regards,

Prince

[derk_roesler]

Hi Prince,

the rulerunner package contains a method zcl_rulerunner=>select_for_all_entries_in.
You can use this method directly in a BRFplus procedure call. It generates an ABAP SQL statement that executes a Select from table (parameter 4 in screenshot) for all entries in int.table (parameter 3 in screenshot) statement. There are up to 5 where-conditions that can be set.
The result table is transferred to the BRFplus Result Data Object.
Please make sure, that the structure of the db table and the BRFplus result table are quite identical (rulerunner automatically converts date and time fields into the BRFplus format).

You may also have a look rulerunner documentation chapter 5.3. (on GitHub).

Hope that helps.

Regards,

Derk

[derk_roesler]

Hi Prince,

You could first read all the data from the 3 tables using a "For all entries in" pattern.
That would avoid multiple DB lookups within a loop.

There is an open source project on Github https://github.com/rulerunner/rulerunner4ABAP.
It offers a simple solution for this pattern. In BRFplus you call a static method passing all details for the "all entries" lookup. It returns the results directly into a BRFplus context object.

Regards,

Derk

[prince_joshi]

Hi Derk,

I could not get time to respond to you on this.

Yes rule runner concept(event->consumer) is there. But how come it fulfill my requirement of avoiding nested loops?

Also, is it true that we should write all the application logic in ABAP and not in BRF+, If that is the case, I am supposed to write all the select queries using for all entries in ABAP and then pass the values to BRF+ to perform some validations?

Because BRF+ give option for DBlookup I thought to fetch all data in BRF+ only and perform validation also. So, no need for any technical person to be involved in future if they wanted to change where clause or want to filter out some data for the rules.

Could you please suggest.

Regards,

Prince

[keremkoseoglu]

BRF+ has the capability to call custom Z-functions / static methods. If you write your own fast method, you can call it from BRF+ and get the results to continue with the rule steps.

In a project of mine, we had to park BRF+ and turn to custom Z-classes due to similar performance issues. The code generated by BRF+ tools is humble. It is a good tool for rule processing, but the generated nested loops can be really slow with big datasets.

[prince_joshi]

Thank you for the reply 🙂

Yes, using 'Call Procedure' I could call the function modules/class methods where I can collect the data. But in future if business wanted to add some more filter creteria for the data selection then they required codes changes again.

I was thinking to achieve all these from BRF+ only. So there won't be any dependency on the developer and business can add more filters from their end.