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