People planning Calculation Performance issues

[royari]

Our People planning calculations are running into performance issues. ( 10K employees) upon investigating we have found that the allocation methods where we are querying an external sql table which has actual data by employee is causing the issue. We have to query actuals for YTD salary as that stored in a custom table in application database. Any query to the PLP_PLAN table is running fine as it is using the register cache datatable. I believe the following code  m_PlanDataTable.Select(criteria) is selecting data from the data table which is being created in the PLP_SOLUTION Helper BR.  I wanted to ask how and in which BR we can use to create a Data table for the custom actual table , so that we have it in memory and use that in our plp calculations. Do you have any snippet.

[ChristianW · Accepted solution]

Hi Royari

You should make yourself familiar with the usage of the globals parameter of the main function (of all business rule types).

Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object

it is used by every business rule and survives all the xfbr calls in PLP allocations. You can query your custom table once and then keep it in memory. It is very powerful to avoid unnecessary calls to external resources.

Dim sName As String = "Global_Table"
Dim myTable as datatable
SyncLock globals.LockObjectForInitialization
	myTable = globals.GetObject(sName )
	If myTable Is Nothing Then
		myTable = GetMyTable(si)
		globals.SetObject(sName, myTable)
	End If
End SyncLock

The SyncLock is needed because PLP calculates in parallel.

I hope this helps and cheers

Christian

[royari]

Hi - Do you mean that we should add this code in the PLP Solution Helper Main function , so the XFBR custom calculation we created which we are calling from the allocation method. ? 

[ChristianW]

No, add it to the place you are querying the custom table (I assume it is a Dashboard XFB String business rule). You should avoid to change the PLP Solution Helper. In future versions of PLP you won't have access to it anymore.

Depending of how you are using the custom table, you might need to change more of the code than just adding the globals.

[TheJonG]

I will emphasize Christian's point of learning to use Global Variables as they can save a lot of processing time especially with Specialty Planning where the same rules are executing thousands of times. Also, there are now special Relational Blend API functions which will retrieve a data table and cache it for you without having to use Global Variables. 

The functions are listed below and will cache the table based on various parameters based on workflow, POV, etc. There is some good documentation in the reference guide on these functions that I suggest checking out