Whilst the code you posted is a bit inefficient, I can't see a reason this loop would take 10 minutes unless you're running the insert query on every iteraton of the loop. Here are some ways you might optimize this, including construction of a single query for all inserts that would run at the end of the loop as a batch insert using a DataTable object. Also, added a convenience TimePeriod struct object for better readability and corrected the TimeDimHelper call & strDate part mentioned above. Hopefully this can help you.
Dim strStartPeriod As String = "2025M1"
Dim strEndPeriod As String = "2026M12"
Dim mbrId As Integer = 12345
Dim Mbr As String = "SalesGrowth"
Dim desc As String = "Sales Growth Trend (YoY)"
' Call the subroutine to build and run the SQL
RunInsert(si, mbrId, Mbr, desc, strStartPeriod, strEndPeriod)
Public Sub RunInsert(si As SessionInfo, mbrId As Integer, Mbr As String, desc As String, strStartPeriod As String, strEndPeriod As String)
Dim startPeriod As New TimePeriod(strStartPeriod, si)
Dim endPeriod As New TimePeriod(strEndPeriod, si)
Dim timeIdCache As New Dictionary(Of String, Integer)
Dim dt As New DataTable("MemberTextbyDate_Table")
' Define columns expected by the destination table
dt.Columns.Add("MemberId", GetType(Integer))
dt.Columns.Add("MemberName", GetType(String))
dt.Columns.Add("MemberDescription", GetType(String))
dt.Columns.Add("Text", GetType(String))
dt.Columns.Add("Date", GetType(String))
For year As Integer = startPeriod.Year To endPeriod.Year
Dim maxMonth As Integer = If(year = endPeriod.Year, endPeriod.Month, 12)
Dim minMonth As Integer = If(year = startPeriod.Year, startPeriod.Month, 1)
For month As Integer = minMonth To maxMonth
Dim strDate As String = $"{year}M{month}"
Dim timeID As Integer
If Not timeIdCache.TryGetValue(strDate, timeID) Then
timeID = TimeDimHelper.GetIdFromName(strDate)
timeIdCache(strDate) = timeID
End If
Dim myVaryByTimeId As Integer = BRApi.Finance.Members.GetMemberId(si, DimType.Time.Id, strDate)
Dim mbrText As String = BRApi.Finance.UD.Text(si, 9, mbrId, 2, -1, myVaryByTimeId)
If Not String.IsNullOrWhiteSpace(mbrText) Then
dt.Rows.Add(mbrId, Mbr, desc, mbrText, strDate)
End If
Next
Next
If dt.Rows.Count > 0 Then
Dim dbLocation As String = "App:MemberTextbyDate_Table" ' or wherever your table is defined
Dim useBulkInsert As Boolean = True
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
BRApi.Database.SaveCustomDataTable(si, dbLocation, dbConnApp, dt, useBulkInsert)
End Using
End If
End Sub
' -------------------------------
' TimePeriod struct
' -------------------------------
Public Structure TimePeriod
Public Property Year As Integer
Public Property Month As Integer
Public Property Id As Integer
Public Sub New(name As String, si As SessionInfo)
If Not Regex.IsMatch(name, "^\d{4}M\d{1,2}$") Then
Throw New ArgumentException("Invalid period format. Expected 'YYYYM#'.", NameOf(name))
End If
Year = Integer.Parse(name.Substring(0, 4))
Month = Integer.Parse(name.Substring(5))
Id = BRApi.Finance.Time.GetIdFromName(si, name)
End Sub
End Structure
