Memory issues with DataAdapter
Posted: Tue 03 Nov 2009 15:12
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
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