Cannot dispose/clear memory of sqldatareader [modified]

neo_6053

I run codes to get all the columns for all my table in database using sqldatareader. But then the sqlserv.exe use up too many memory. i tried to dispose everything at the end of the program but still not work. or is it anything to do with the sql server 2005 itself?

this is the sample of my code.. i know it sounds noob, but i just want to get it work.. =_=''

    Dim oraCommand As OracleCommand 
    Dim oraDr As OracleDataReader 
    Dim sqlCommand As SqlCommand 
    Dim sqlDr As SqlDataReader 
    Dim oraFieldCount, sqlFieldCount As Integer 
    Dim oraArr, sqlArr As New ArrayList 
    Dim strMissColPK, strMissColNotPK, strMissCol As String 
    Dim oraCount, sqlCount As Integer 

    Me.RichTextBox1.Clear() 
    If Me.chkData.Checked = False And Me.chkCol.Checked = False And Me.chkTable.Checked = False Then 
        Exit Sub 
    End If 

    For i As Integer = 0 To Me.ListBox1.SelectedItems.Count - 1 
        Try 
            oraCount = 0 
            sqlCount = 0 
            oraCon.Open() 
            oraCommand = New OracleCommand("select \* from " & Me.ListBox1.SelectedItems(i).ToString, oraCon) 
            oraDr = oraCommand.ExecuteReader 
            oraDr.Read() 
            oraFieldCount = oraDr.FieldCount 
            oraArr.Clear() 
            For j As Integer = 0 To oraDr.FieldCount - 1 
                oraArr.Add(oraDr.GetName(j).ToLower) 
            Next 
            oraDr.Close() 
            oraCommand = New OracleCommand("select count(\*) from " & Me.ListBox1.SelectedItems(i).ToString, oraCon) 
            oraDr = oraCommand.ExecuteReader 
            If oraDr.Read Then 
                oraCount = oraDr(0) 
            End If 

            sqlCOn.Open() 
            sqlCommand = New SqlCommand("select \* from " & Me.ListBox1.SelectedItems(i).ToString, sqlCOn) 
            sqlDr = sqlCommand.ExecuteReader()  ## Here is where the system use about 2mb for each table. I have 400+ table ##
            sqlDr.Read() 
            sqlFieldCount = sqlDr.FieldCount 
            sqlArr.Clear() 
            For j As Integer = 0 To sqlDr.FieldCount - 1 
                sqlArr.Add(sqlDr.GetName(j).ToLower) 
            Next 
            sqlDr.Close() 
            sqlCommand = New SqlCommand("select count(\*) from " & Me.ListBox1.SelectedItems(i).ToString, sqlCOn) 
            sq

Mycroft Holmes

Without reading through your code I have a couple of points that may be relevant. 1 You are using sql strings - you should be using stored proc. 2 I would use datatables for every single one of your cmd.executereader. A datareader maintains the connection while you are procesing the data, a datatable is disconnected from the connection as soon as you complete the load.

Never underestimate the power of human stupidity RAH

neo_6053

i didnt use datatable because the table is too large. but suddenly i realize i can just "select top 1" =_= btw, will this solve the problem of memory leak?

Wat key(s) do gamer press most? WASD Wat key(s) do Programmer press most? Ctrl C + Ctrl V

Dave Kreskowiak

If you're looking at TaskManager to see the memory statistics, you're looking at the wrong stats. TaskManager is showing you about how much memory is reserved by the .NET CLR that is running your app. It is NOT the amount of memory your app is actually using. If you want to see much more accurate stats, use Performance Monitor instead. There are a bunch of .NET Memory counters you can look at that won't "lie" to you.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008

neo_6053

but it's actually the sqlserv.exe that use up the memory. it reach above 1GB if i continue to run the program. i try to run the code in debug mode, if i stop it, it wouldn't free up the memory also.

Wat key(s) do IM user press most? > ENTER - to send msg Wat key(s) do gamer press most? > WASD Wat key(s) do Programmer press most? . . . > Ctrl C + Ctrl V

Christian Graus

If the memory usage is in SQL Server, then you need to optimise your SQL and how you call SQL Server to limit memory usage.

Christian Graus Driven to the arms of OSX by Vista.

neo_6053

Thx. But what should i do? How to optimize? FYI, i 've change to use sqldataadapter, it seems to use less memory, but still it wont free up after used. and btw, as can seen from my code, i used oracle reader too. it does not have any problem.

Wat key(s) do gamer press most? > WASD Wat key(s) do Programmer press most? > Ctrl C + Ctrl V

Christian Graus

Well, one way would be to write a bunch of SQL so you send it all at once and get a single dataset back instead of using readers over and over for every result you want.

Christian Graus Driven to the arms of OSX by Vista.

Guffa

Make sure that you dispose all the disposable objects that you use. I see at least one OracleCommand object that you leave open and undisposed. As you do this in a loop, that's one connection for each selected item. Don't call GC.Collect. The garbage collector knows better when a garbage collection should be done.

Despite everything, the person most likely to be fooling you next is yourself.

neo_6053

sorry for misleading. Actually it's the sqlserv.exe that use up the memory. My oracle.exe is working fine. MICROSOFT ......

Wat key(s) do gamer press most? > WASD Wat key(s) do Programmer press most? > Ctrl C + Ctrl V

Guffa

It's normal for SQL Server to use any available memory for caching, that is the default setting. If you are running the SQL Server on the same computer as the application, you have to limit the amount of memory that SQL Server uses, so that there is some left for the application. Normally it takes days or weeks until SQL Server uses all available memory, but as you are fetching everything everytime in your query, it will go a lot faster. Verify that you close and dispose everything, not just the one object that I found at a quick read-through. I also see an SqlCommand object that you leave open and undisposed. That means that one connection is left open for each selected item, which uses a bit of memory for buffers, and also keeps the result in memory as you leave it unread. The connections will time out after a while, but you are probably running into problems before the timeouts starts to free up enough connections.

Despite everything, the person most likely to be fooling you next is yourself.

Guffa

neo_6053 wrote:

Wat key(s) do Programmer press most?

Depends on the programmer... For some it's the Z key... as in "plzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz hlp me!!!!".

Despite everything, the person most likely to be fooling you next is yourself.

neo_6053

i've tried to do some testing. this is my testing code

Dim sqlCOn As New SqlConnection("Server=localhost;Database=phxtest;Trusted_Connection=yes")
Dim sqlda As SqlDataAdapter
Dim sqldt As DataSet
Dim sqlFieldCount As Integer
sqlCOn.Open()
Dim SqlCommand As New SqlCommand("select top 0 * from msaspot", sqlCOn)
sqlda = New SqlDataAdapter(SqlCommand)
sqlDt = New DataSet
sqlDa.Fill(sqlDt) ## Here is where it use up the memory ##
sqlDa.Dispose()
sqlFieldCount = sqldt.Tables(0).Columns.Count
sqldt.Clear()
sqldt.Dispose()
SqlCommand.Dispose()
sqlCOn.Close()
sqlCOn.Dispose()

Wat key(s) do gamer press most? > WASD Wat key(s) do Programmer press most? > Ctrl C + Ctrl V