GetDataBuffer takes too much time to process

[Kamila]

Dear Team,

I'm trying to understand what caused the low performance of my code. I see that it's because of many loops, but I'm wondering if there's any possible way to fix it:

		Dim ResultDataBuffer As DataBuffer = New DataBuffer()
		Dim sAccounts As List(Of String) = New List(Of String) From {							
							"1711050",
							"2742020", 
							"1712020", 
							"2745060", 
							"1346010", 
							"2413010", 
							"1742050", 
							"2716050", 
							"1745010", 
							"2713010", 
							"3221010", 
							"3221020", 
							"3221030", 
							"4451010", 
							"4451020", 
							"4451030", 
							"1722060", 
							"2741030", 
							"4541070", 
							"4321020", 
							"4541010", 
							"4541020", 
							"3222010", 
							"4452010", 
							"4541030", 
							"4541040", 
							"4541050", 
							"4541060", 
							"5111010", 
							"5111020"}
							
		Dim mICFilter As List (Of Member) = api.Members.GetBaseMembers(api.Pov.EntityDim.DimPk,api.Members.GetMemberId(dimtype.entity.Id,"Top_member"))

					For Each Acc As String In sAccounts 
						
						For Each Ic As Member In mICFilter
							
						Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo("A#" & Acc & ":O#Forms:I#" & Ic.Name & ":UD1#None:UD2#None")
						
						'Starting data buffer
						Dim StartingDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula("RemoveZeros(A#" & Acc & ":O#Forms:I#" & Ic.Name & ":UD1#None:UD2#None)")
							For Each sourceCell As DataBufferCell In StartingDataBuffer.DataBufferCells.Values
								
							Dim resultCell As New DataBufferCell(sourceCell)

							resultcell.SetOrigin(api,"Forms")
							resultcell.SetIC(api,Ic.Name)
							resultcell.SetAccount(api,Acc)
									
							'Calculate amount and set to is NoData = true
							resultcell.CellAmount = 0
							resultCell.CellStatus = DataCellStatus.CreateDataCellStatus(True, False)
									
							'Set target member
							ResultDataBuffer.SetCell(si,resultcell)						
								
							Next
							'Save the Data Buffer using Result Data Buffer and Destination Info
							api.Data.SetDataBuffer(ResultDataBuffer,DestinationInfo)
						Next						
					Next					

[RobbSalzmann]

Databuffers are made to be efficient in processing all the cells in one collection(the buffer).  Create one databuffer will all your accounts and ICs in it instead of looping over the accounts and ICs  one at a time.

This should get you close:

Dim ResultDataBuffer As DataBuffer = New DataBuffer()
Dim sAccounts As List(Of String) = New List(Of String) From {							
					"1711050",
					"2742020", 
					"1712020", 
					"2745060", 
					"1346010", 
					"2413010", 
					"1742050", 
					"2716050", 
					"1745010", 
					"2713010", 
					"3221010", 
					"3221020", 
					"3221030", 
					"4451010", 
					"4451020", 
					"4451030", 
					"1722060", 
					"2741030", 
					"4541070", 
					"4321020", 
					"4541010", 
					"4541020", 
					"3222010", 
					"4452010", 
					"4541030", 
					"4541040", 
					"4541050", 
					"4541060", 
					"5111010", 
					"5111020"}
Dim accountsFilter As String = String.Join(",", sAccounts.Select(Function(account) $"A#{account}"))					
Dim iCsFilter As String = String.Join(",", icMembers.Select(Function(ic) $"I#{ic}"))					

					
Dim DestinationInfo As ExpressionDestinationInfo = api.Data.GetExpressionDestinationInfo($"Filtermembers(O#Forms:UD1#None:UD2#None,[{accountsFilter}],[I#Top_member].Base)")
Dim StartingDataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula($"RemoveZeros(O#Forms:UD1#None:UD2#None,[{accountsFilter}],[I#Top_member].Base))")
For Each sourceCell As DataBufferCell In StartingDataBuffer.DataBufferCells.Values
	Dim resultCell As New DataBufferCell(sourceCell)

	resultcell.SetOrigin(api,"Forms")
	resultcell.SetIC(api,Ic.Name)
	resultcell.SetAccount(api,Acc)
	resultcell.CellAmount = 0
	resultCell.CellStatus = DataCellStatus.CreateDataCellStatus(True, False)
			
	'Set target member
	ResultDataBuffer.SetCell(si,resultcell)						
		
	Next
'Save the Data Buffer using Result Data Buffer and Destination Info
api.Data.SetDataBuffer(ResultDataBuffer,DestinationInfo)

[JackLacava]

The immediate suggestion would be to use FilterMembers instead of looping through accounts and ICs:

Dim sourceBuffer as DataBuffer = api.Data.GetDataBufferUsingFormula( _
    "FilterMembers(RemoveZero(A#All:O#Forms:U1#Whatever:etcetc), A#account1, A#account2, I#ic1, I#ic2, [etc etc]")

If you really want, you can then loop through the records in that buffer to set all cells to zero.

[Henning]

Hi, in addition to the above responses, I would also suggest to contemplate why there is data that needs to be set to NoData in the database in the first place. There may be good reasons for the need to do what you are trying to do here, but in most cases, this kind of data should e.g. be bypassed in the transformation rules, excluded from a copy routine (e.g. ACT to FC), or reclassified in order to provide a transparent and auditable data flow. Just deleting data may not always be in the best interest of the end user(s).

Also, as you know, calculated data data can be cleared with api.Data.ClearCalculatedData(), but here, O#Forms seems to be specified in order to remove data that has been manually entered (possibly?)? This may hint at changing other things such as adjusting the data access security so that users cannot enter data in unwanted combinations in the first place.

I am sure you have considered all of this already, so please also view this response in the light of the wider audience of this forum as I like to add words of caution for others reading this post to address their own solutions. 🙂