Inserting data into a SQL table while avoiding the 'System.OutOfMemoryException' error.

Marco
Contributor

Hi everyone.

I have this error when trying to insert data into my SQL table 'System.OutOfMemoryException'. This occurs when I use .base to retrieve all my departments.

The following is how I enter the values of a table into SQL using a loop, so I would like to know what I can do to avoid that error when I want to add many values to that table.

For Each isRow As DataRow In dt.Rows
Dim sqlInsert As String = $"INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, [View], Account, Flow, Origin, Ic, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8) VALUES " &
                           $"(1, {Amount}, '{Annotation}', '{SourceRule}', '{DriverRule}', '{Type}', '{StepDesc}', '{TargetRule}', '{AllocRule}', {Generation}, '{Cube}', '{Entity}', '{Consolidation}', '{Scenario}', '{Time}', '{View}', '{Account}', '{Flow}', '{Origin}', '{Ic}', '{UD1}', '{UD2}', '{UD3}', '{UD4}', '{UD5}', '{UD6}', '{UD7}', '{UD8}')"

'BRApi.ErrorLog.LogMessage(si, "SQL INTO: " & sqlInsert)

Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
    BRApi.Database.ExecuteSql(dbConnApp, sqlInsert, False)
End Using

Next

 

2 ACCEPTED SOLUTIONS

RobbSalzmann
Valued Contributor

Hi Marco,
Instead of running the query  many times inside a loop, use the loop to curate a single query and run the query once at the end:

Dim sqlInsert As New StringBuilder()
Dim insertVals As New List(Of String)
'start the query
sqlInsert.AppendLine("INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, [View], Account, Flow, Origin, Ic, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8) VALUES ")

For Each isRow As DataRow In dt.Rows
     'add each insert group
     insertVals.Add($"(1, {Amount}, '{Annotation}', '{SourceRule}', '{DriverRule}', '{Type}', '{StepDesc}', '{TargetRule}', '{AllocRule}', {Generation}, '{Cube}', '{Entity}', '{Consolidation}', '{Scenario}', '{Time}', '{View}', '{Account}', '{Flow}', '{Origin}', '{Ic}', '{UD1}', '{UD2}', '{UD3}', '{UD4}', '{UD5}', '{UD6}', '{UD7}', '{UD8}')")
Next

'finish the query
sqlInsert.AppendLine(String.Join($",{Environment.NewLine}", insertVals.ToArray()))

'run the query once for all inserts.
Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
    BRApi.Database.ExecuteSql(dbConnApp, sqlInsert.ToString(), True)
End Using

 

Software Architect
MindStream Analytics
"Difficult done right away. Impossible takes a bit longer"

View solution in original post

FrankDK
Contributor

Just to append to @RobbSalzmann answer, you might want to consider using the sql function call, that includes parameters, and if you still get time-outs, use the "useCommandTimeOutLarge" feature

FrankDK_0-1700483975295.png

 

View solution in original post

4 REPLIES 4

RobbSalzmann
Valued Contributor

Hi Marco,
Instead of running the query  many times inside a loop, use the loop to curate a single query and run the query once at the end:

Dim sqlInsert As New StringBuilder()
Dim insertVals As New List(Of String)
'start the query
sqlInsert.AppendLine("INSERT INTO AllocationResults (Step, Amount, Annotation, SourceRule, DriverRule, Type, StepDesc, TargetRule, AllocRule, Generation, Cube, Entity, Consolidation, Scenario, Time, [View], Account, Flow, Origin, Ic, UD1, UD2, UD3, UD4, UD5, UD6, UD7, UD8) VALUES ")

For Each isRow As DataRow In dt.Rows
     'add each insert group
     insertVals.Add($"(1, {Amount}, '{Annotation}', '{SourceRule}', '{DriverRule}', '{Type}', '{StepDesc}', '{TargetRule}', '{AllocRule}', {Generation}, '{Cube}', '{Entity}', '{Consolidation}', '{Scenario}', '{Time}', '{View}', '{Account}', '{Flow}', '{Origin}', '{Ic}', '{UD1}', '{UD2}', '{UD3}', '{UD4}', '{UD5}', '{UD6}', '{UD7}', '{UD8}')")
Next

'finish the query
sqlInsert.AppendLine(String.Join($",{Environment.NewLine}", insertVals.ToArray()))

'run the query once for all inserts.
Using dbConnApp As DBConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
    BRApi.Database.ExecuteSql(dbConnApp, sqlInsert.ToString(), True)
End Using

 

Software Architect
MindStream Analytics
"Difficult done right away. Impossible takes a bit longer"

FrankDK
Contributor

Just to append to @RobbSalzmann answer, you might want to consider using the sql function call, that includes parameters, and if you still get time-outs, use the "useCommandTimeOutLarge" feature

FrankDK_0-1700483975295.png

 

Hi Frank, this resolves this error, and if not, is there a way to prevent it from appearing?
User Interface Stack Trace: at System.Net.Sockets.Socket.EndReceive(IAsyncResult asyncResult) at System.Net.Sockets.NetworkStream.EndRead(IAsyncResult asyncResult)

I see these options:

- Increase the time-out (on server configuration level)

- Split the "insert" into groups to avoid the time-out

But how many rows do you need to insert and what's the scenario for this?