Memory issues with DataAdapter

[gravem]

I've been getting out of memory problems when loading a large amount of rows into Oracle. Some of the text files we load have over 2 million rows.

I've run the same thing in System.Data.OracleClient and haven't had the same problem.

For example (memory usage) :
Application starts: 14,344K

Devart inserting 50,000 rows: 50,668K
Devart inserting 100,000 rows: 69,200K

OraClient inserting 50,000 rows: 32,480K
OraClient inserting 100,000 rows: 32,220K

There is no memory issue if I am just calling the insert procedure for every row I want to insert... but to batch rows... I am using a dataadapter with UpdateBatchSize=100... because it is way faster.

Below is the code used for the test. The table being inserted into has 3 columns only.... the procedure being called is just inserting a row into this table.

Code for DevArt test:
'******************************************
Public Sub TestDevArt
Dim conn As New Devart.Data.Oracle.OracleConnection
conn.ConnectionString = "DATA SOURCE=TEST;PASSWORD=test;USER ID=ORACLE"

Dim insertCmd As Devart.Data.Oracle.OracleCommand = New Devart.Data.Oracle.OracleCommand
With insertCmd
.Connection = conn
.CommandText = "ORACLE.EDI_PREPROCESSING.I_EDI_CLIENT_FILE_DATA"
.CommandType = CommandType.StoredProcedure

.Parameters.Clear()
.Parameters.Add("p_file_id", Devart.Data.Oracle.OracleDbType.Number, 0, "FILE_ID").Direction = ParameterDirection.Input
.Parameters.Add("p_data_seq", Devart.Data.Oracle.OracleDbType.Number, 0, "DATA_SEQ").Direction = ParameterDirection.Input
.Parameters.Add("p_data_value", Devart.Data.Oracle.OracleDbType.VarChar, 2000, "DATA_VALUE").Direction = ParameterDirection.Input
End With

Dim dt As New DataTable
dt.Columns.Add(New DataColumn("FILE_ID", GetType(Int64)))
dt.Columns.Add(New DataColumn("DATA_SEQ", GetType(Int64)))
dt.Columns.Add(New DataColumn("DATA_VALUE", GetType(String)))

Dim da As New Devart.Data.Oracle.OracleDataAdapter()
da.InsertCommand = insertCmd
da.InsertCommand.Connection = conn
da.InsertCommand.UpdatedRowSource = UpdateRowSource.None
da.UpdateBatchSize = 100

Dim dr As DataRow

Try
conn.Open()

For xx As Integer = 1 To 100000
dr = dt.NewRow
dr("FILE_ID") = 1
dr("DATA_SEQ") = xx
dr("DATA_VALUE") = "DFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERYDFGDFGDFGDFGDFGDFGDFHDJYRYERY"
dt.Rows.Add(dr)

If dt.Rows.Count > 1000 Then
da.Update(dt)
dt.Rows.Clear()
End If
Next

Finally
conn.Close()
conn.Dispose() : conn = Nothing
End Try
Return
End Sub
' *************************************


*UPDATE*

I found that if I move the code to create a new dataadapter inside the FOR LOOP... the memory starts being released. However, we shouldn't have to create a new dataadapter every time we want to call the Update command.

I'm hoping this is fixed in an upcoming release.

Thanks

Jeff

[Shalex]

We will investigate the issue and notify you about the results as soon as possible.

[Shalex]

We have fixed the bug with using OracleDataAdapter in Butch update mode on the update operation. Look forward to the next build of dotConnect for Oracle. I will post here when it is available for download.

As a temporary workaround, please use a new instance of OracleDataAdapter during every call of Update.

[Shalex]

dotConnect for Oracle v 5.35 is released.
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=16436.