OLEDB Memory Issues? Any sugguestions? -- dotnet odbc.net

twhidden NO[at]SPAM -NOSPAM-smartconnect.net
9/21/2004 9:03:30 AM

Hello all.

I have created an OLE Data Wrapper what I use on nearly all my projects. I have never really noticed any problems with it, till I started inserting 100,000+ records into my database daily.

The problem is that after about 4,000 records, the physical memory of my application goes up 180kb and my VM also about 180 +/- 8kb. At first I was thinking that was just the normal run or the .net framework, but after a weekend of running in a production environment, I found that my application which started at 22,724 Physical / 14,928 Virtual would be at 80+ / 90+ Megs. I am not to sure on the total records inserted at this point, but it would most likely be around 500,000 entries.

Here is a cut of my data wrapper. I have narrowed it down to this because I removed all the save routines, and the memory never would rise (above anything unreasonable).

----------------------------------------------------------

Public Sub SaveDataSet(ByRef ds As DataSet, ByVal sql As String, Optional ByVal ContinueOnUpdate As Boolean = False)
Dim arySQL() As String = Split(sql, ";")
Dim da As OleDbDataAdapter
Dim cb As OleDbCommandBuilder
Dim i As Integer = 0
For Each sqlstring As String In arySQL
sqlstring = Trim(sqlstring)
If sqlstring <> "" Then
da = New OleDbDataAdapter(sqlstring, ConnectionString)
cb = New OleDbCommandBuilder(da)
da.ContinueUpdateOnError = ContinueOnUpdate
da.UpdateCommand = cb.GetUpdateCommand
da.DeleteCommand = cb.GetDeleteCommand
da.InsertCommand = cb.GetInsertCommand
da.Update(ds.Tables(i))

da.UpdateCommand.Dispose()
da.UpdateCommand = Nothing
da.SelectCommand.Dispose()
da.SelectCommand = Nothing
da.InsertCommand.Dispose()
da.InsertCommand = Nothing
da.DeleteCommand.Dispose()
da.DeleteCommand = Nothing

da.TableMappings.Clear()
da.Dispose()
da = Nothing

cb.Dispose()
cb = Nothing
End If
i += 1
Next
Erase arySQL
End Sub

-----------------------------------------------------------------

I have a .net Memory Profiler, and the internal objects are nothing out of the ordinary. Its just looks like the OS is reporting more and more usage. Do you see anything wrong with this code that could be causing an increased amount of memory usage after so many executions? I have added a hand full of cleanup code etc, and it really did nothing for me.

---
Posted using Wimdows.net NntpNews Component -

Travis Whidden
9/23/2004 7:47:13 AM

Using this same code, and using the SQLDataAdapter fixed the problem. I had
read that someone else had this same issue with the OLE Driver. I will run
more extensive tests to confirm this, however so far my initial test have
proven positive.

If this is the case, then there is some sort of memory leak in the OLE
Drivers. I was told by someone that the OLE Drivers actually use some
unmanaged code. Is this true? The SQLClient Drivers do not in .Net.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet odbc.net

dotnet odbc.net : OLEDB Memory Issues? Any sugguestions?