Cannot dispose/clear memory of sqldatareader [modified]
-
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) sqWithout 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
-
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
-
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
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 -
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, 2008but 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
-
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) sqIf 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.
-
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.
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
-
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
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.
-
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) sqMake 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.
-
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.
-
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
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.
-
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
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.
-
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) sqi'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