ASP.NET Memory Leak - OracleCommand

Question

I have inherited some code like ths:

Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
            Try
                Dim Test As Integer
                Dim strCon As String = "Data Source=TestDatabase;User Id=TestUser;Password=TestPassword;"
            For Test = 0 To 100000
            Dim objDBCon As New OracleConnection(strCon)
            Dim objDBCmd As New OracleCommand
            Dim objDR As OracleDataReader
            Using objDBCon
                'Using objDBCmd
                objDBCmd.Connection = objDBCon
                objDBCmd.CommandText = "SELECT * FROM nominal_incident where rownum = 1 "
                objDBCon.Open()
                objDR = objDBCmd.ExecuteReader
                'End Using

            End Using
        Next   
            Catch ex As Exception
              'Handle the exception.  It is not absorbed.
            End Try
        End Sub

Is this going to cause a memory leak? I would expect to see Using statements as follows:

Using objDBCmd

End Using

The ASP.NET process grows gradually (to over 1 GB) and WinDBG shows that there are OracleCommand objects on the heap (though not as many as I expected). I suppose the OracleCommand could keep hold of other resources that would cause the process size to grow.

zeal zeal · Accepted Answer · 2012-07-16T13:29:22

Using is used to release the object once you are done with it, so that wouldn't be the issue. However using does close the objDBCon connection. It's more efficient to keep that connection open and share it with each call to the DB. So in this case I would actually get rid of the using for objDBCon and pull it outside of the for loop, just make sure you close it outside of the loop after all the db calls.

Also, I'm not sure if this is a direct copy from your code and I can't tell what your code is doing, but 100k calls to the db is probably why its so high on memory use and will cause a high load on sql server.

ASP.NET Memory Leak - OracleCommand

2 Answers