How to Remove an SQLData Object From Memory

A

Alan Liddle

Member
Joined
Feb 19, 2023
Messages
7
Location
Pottsville NSW Australia
Programming Experience
10+
My app retrieves data from an MS SQL Server using the SQLData object. One particular process requires loading into memory quite a large amount of data. The actual query takes about 30 seconds to execute and loads about 8GB into memory before the program reads the required data into in-memory arrays. Once loaded, the in-memory arrays take up another 4GB, totalling 12GB approx. The initial 8GB is finally cleared after about 10 minutes by VB.Net. But as I need to load and execute multiple instance of the app at once (up to 12), I need the initial 8GB to be removed from memory as soon as the app has filled the in-memory arrays.

I have used GC.Collect() but that doesn't help.

How do I totally clear the SQLData object and its buffer from memory?

Regards

Alan :)
 
Sort by date Sort by votes
Sorry, I don't know how the SQLConnection retrieves the data, but it definitely causes a large increase in memory usage.

Here is my SQLData Class:

VB.NET: 
Imports System.Data
Imports System.Diagnostics
Imports Microsoft.Data.SqlClient

Public Class sqlData

  Private DBCon As New SqlConnection(ConString)

  Private DBCmd As SqlCommand

  ' DB DATA
  Public DBDA As SqlDataAdapter

  Public DBDT As DataTable

  ' QUERY PARAMETERS
  Public Params As New List(Of SqlParameter)

  ' QUERY STATISTICS
  Public RecordCount As Integer

  Public Exception As String

  Public Sub New()
  End Sub

  Public Sub Query(theQuery As String)
    RecordCount = 0
    Exception = ""
    Try
      DBCon.Open()
      ' CREATE DB COMMAND
      DBCmd = New SqlCommand(theQuery, DBCon)
      ' LOAD PARAMS INTO DB COMMAND
      Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
      ' CLEAR PARAM LIST
      Params.Clear()
      ' EXECUTE COMMAND & FILL DATASET
      DBDT = New DataTable
      DBDA = New SqlDataAdapter(DBCmd)
      RecordCount = DBDA.Fill(DBDT)
    Catch ex As Exception
      ' CAPTURE ERROR
      Exception = "ExecQuery Error: " & vbCrLf & ex.Message
    Finally
      ' CLOSE CONNECTION
      If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Try
  End Sub

  Public Sub AddParam(theName As String, Value As Object)
    Dim NewParam As New SqlParameter("@" & theName, Value)
    Params.Add(NewParam)
  End Sub

  Public Function HasException(Optional Report As Boolean = False) As Boolean
    If String.IsNullOrEmpty(Exception) Then Return False
    If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
    Return True
  End Function

  Public Function FieldsFromParams(Optional asParamName As Boolean = False) As String
    Dim theNames As String
    theNames = ""
    For Each param In Params
      If theNames = "" Then
        If asParamName = True Then
          theNames = param.ParameterName
        Else
          theNames = Replace(param.ParameterName, "@", "")
        End If
      Else
        If asParamName = True Then
          theNames = theNames & "," & param.ParameterName
        Else
          theNames = theNames & "," & Replace(param.ParameterName, "@", "")
        End If
      End If
    Next

    FieldsFromParams = theNames
  End Function

  Public Sub InsertRow(theTable As String)
    Query("INSERT INTO " & theTable & "(" & FieldsFromParams() & ") VALUES(" & FieldsFromParams(True) & ")")
  End Sub

End Class

In my sub to read the data into memory, I use this code:

VB.NET: 
  Private Sub ReadData()
    ConString = "Server=MAINSERVER;Database=MainDB;Integrated Security=True;Encrypt=False"
    Dim DBCon As New sqlData
    DIM i1 as Long
   
    WITH DBCon
      .Query("SELECT * FROM Transactions ORDER BY theDate")
      For i1 = 0 To .RecordCount - 1
          ' Read all data into in-memory arrays
        ....
      Next
    End With
    GC.Collect
  End Sub

Immediately prior to the line of code that executes the query (Highlighted above), the app's memory usage is 47.8MB. It takes 30 seconds for the query to execute, at which point (Prior to reading data into the in-memory arrays), the memory usage of the app has increased to 8,222.1MB and the app then executes the For/Next loop to read in excess of 10,000,000 records into the in-memory arrays. After the For/Next loop is complete the memory usage is up to 13,412.5MB. At the end of the routine I execute a GC.Collect which I thought would clear the excess 8GB of memory usage. It can take in excess of 10 minutes for the excess to clear, at which point the memory usage drops to about 4,500MB.

I know the SQLConnection is the cause of the increase in memory usage and given I need to be able to run up to 12 instances of this app concurrently, I need to be able to clear the excess memory usage on demand.

How?

Regards

Alan :)
 
Upvote 0 Downvote
The SqlConnection doesn't retrieve any data. It represents the connection to the database. The SqlDataAdapter contains one or more SqlCommands and they contains SQL code that is executed on the database to retrieve or save data. When you call Fill on that, the SelectCommand is executed and it retrieves data and stores it in a DataTable. It's that DataTable, which is basically an in-memory representation of a database table, that contains the data. More specifically, it's Rows collection contains DataRows and they contain the data for one record each. Sounds like you could afford to do a bit of reading on ADO.NET and learn what the individual components actually do.

In your case, your GC.Collect call can't help because there is still a reference to the sqlData object and that still has a reference to the DataTable. In order for the garbage collector to reclaim the memory occupied by an object, there needs to be no more references to that object in your code. You might have some chance if you were to set DBCon to Nothing but I doubt that a single call to GC.Collect would do the job anyway. You could possibly have that sqlData class implement IDisposable and then, when you dispose it, have it Clear the DataTable. That would get you closer to being able to reclaim that memory.
 
Upvote 0 Downvote
By the way, sqlData is a terrible name for that class. For one thing, have you not noticed how every other type you use has a name that starts with an upper-case letter? All names should also be descriptive of what they represent. That class is far more than just data. I'd be inclined to name it DataManager or DataAccessProvider or the like. If you want to follow Microsoft's convention and specifically indicate that it's for SQL Server, you might use SqlDataManager or the like. DBCon is also a bad name for the variable because the object is far more than a database connection. I'd be inclined to have something like Dim dataManager As New SqlDataManager.
 
Upvote 0 Downvote
jmcilhinney said:
By the way, sqlData is a terrible name for that class. For one thing, have you not noticed how every other type you use has a name that starts with an upper-case letter? All names should also be descriptive of what they represent. That class is far more than just data. I'd be inclined to name it DataManager or DataAccessProvider or the like. If you want to follow Microsoft's convention and specifically indicate that it's for SQL Server, you might use SqlDataManager or the like. DBCon is also a bad name for the variable because the object is far more than a database connection. I'd be inclined to have something like Dim dataManager As New SqlDataManager.
Click to expand...

jmcilhinney said:
The SqlConnection doesn't retrieve any data. It represents the connection to the database. The SqlDataAdapter contains one or more SqlCommands and they contains SQL code that is executed on the database to retrieve or save data. When you call Fill on that, the SelectCommand is executed and it retrieves data and stores it in a DataTable. It's that DataTable, which is basically an in-memory representation of a database table, that contains the data. More specifically, it's Rows collection contains DataRows and they contain the data for one record each. Sounds like you could afford to do a bit of reading on ADO.NET and learn what the individual components actually do.

In your case, your GC.Collect call can't help because there is still a reference to the sqlData object and that still has a reference to the DataTable. In order for the garbage collector to reclaim the memory occupied by an object, there needs to be no more references to that object in your code. You might have some chance if you were to set DBCon to Nothing but I doubt that a single call to GC.Collect would do the job anyway. You could possibly have that sqlData class implement IDisposable and then, when you dispose it, have it Clear the DataTable. That would get you closer to being able to reclaim that memory.
Click to expand...

Thanks for the help. Unfortunately it hasn't helped. Here is my edited code, with new code highlighted.

VB.NET: 
Imports System.Data
Imports System.Diagnostics
Imports Microsoft.Data.SqlClient

Public Class sqlData

  Implements IDisposable
  Private DBCon As New SqlConnection(ConString)

  Private DBCmd As SqlCommand

  ' DB DATA
  Public DBDA As SqlDataAdapter

  Public DBDT As DataTable

  ' QUERY PARAMETERS
  Public Params As New List(Of SqlParameter)

  ' QUERY STATISTICS
  Public RecordCount As Integer

  Public Exception As String


  Public Sub New()
  End Sub

  Public Sub Dispose() Implements IDisposable.Dispose
    DBDT.Dispose()
  End Sub

  Public Sub Query(theQuery As String)
    RecordCount = 0
    Exception = ""
    Try
      DBCon.Open()
      ' CREATE DB COMMAND
      DBCmd = New SqlCommand(theQuery, DBCon)
      ' LOAD PARAMS INTO DB COMMAND
      Params.ForEach(Sub(p) DBCmd.Parameters.Add(p))
      ' CLEAR PARAM LIST
      Params.Clear()
      ' EXECUTE COMMAND & FILL DATASET
      DBDT = New DataTable
      DBDA = New SqlDataAdapter(DBCmd)
      RecordCount = DBDA.Fill(DBDT)
    Catch ex As Exception
      ' CAPTURE ERROR
      Exception = "ExecQuery Error: " & vbCrLf & ex.Message
    Finally
      ' CLOSE CONNECTION
      If DBCon.State = ConnectionState.Open Then DBCon.Close()
    End Try
  End Sub

  Public Sub AddParam(theName As String, Value As Object)
    Dim NewParam As New SqlParameter("@" & theName, Value)
    Params.Add(NewParam)
  End Sub

  Public Function HasException(Optional Report As Boolean = False) As Boolean
    If String.IsNullOrEmpty(Exception) Then Return False
    If Report = True Then MsgBox(Exception, MsgBoxStyle.Critical, "Exception:")
    Return True
  End Function

  Public Function FieldsFromParams(Optional asParamName As Boolean = False) As String
    Dim theNames As String
    theNames = ""
    For Each param In Params
      If theNames = "" Then
        If asParamName = True Then
          theNames = param.ParameterName
        Else
          theNames = Replace(param.ParameterName, "@", "")
        End If
      Else
        If asParamName = True Then
          theNames = theNames & "," & param.ParameterName
        Else
          theNames = theNames & "," & Replace(param.ParameterName, "@", "")
        End If
      End If
    Next

    FieldsFromParams = theNames
  End Function

  Public Sub InsertRow(theTable As String)
    Query("INSERT INTO " & theTable & "(" & FieldsFromParams() & ") VALUES(" & FieldsFromParams(True) & ")")
  End Sub

End Class

  Private Sub ReadData()
    ConString = "Server=MAINSERVER;Database=MainDB;Integrated Security=True;Encrypt=False"
    Dim DBCon As New sqlData
    DIM i1 as Long
   
    WITH DBCon
      .Query("SELECT * FROM Transactions ORDER BY theDate")
      For i1 = 0 To .RecordCount - 1
          ' Read all data into in-memory arrays
        ....
      Next
     .Dispose()
    End With
    GC.Collect
  End Sub

Have I done something wrong? Not sure what to try next.

If I were working in a team of developers, or my app was to ever be worked on by others I probably would be wise to use more understandable names, but eh, I am a retired software developer (who was self taught) and working on share market analysis to keep me busy and maybe make some money.

As far as understanding how a data connection works (or any other tools I use), I tend not to waste the time, providing I get the results I need. Up 'til now the data connection has worked for me as I have been limiting my analysis to 4 sessions at a time. Now I am expanding.

Thanks heaps for trying to help me.

Alan :)
 
Upvote 0 Downvote
Simply calling Dispose on the DataTable isn't going to help. That just calls the inherited Dispose method from the Component class. It doesn't do anything with the data in the table. That's why I said that you'd need to Clear the DataTable. That will remove all the rows and columns and get you closer to a point that their memory can be reclaimed. Like I said though, one call to GC.Collect may still not be enough. Garbage collection is a complex, multi-step process.
 
Upvote 0 Downvote
jmcilhinney said:
Simply calling Dispose on the DataTable isn't going to help. That just calls the inherited Dispose method from the Component class. It doesn't do anything with the data in the table. That's why I said that you'd need to Clear the DataTable. That will remove all the rows and columns and get you closer to a point that their memory can be reclaimed. Like I said though, one call to GC.Collect may still not be enough. Garbage collection is a complex, multi-step process.
Click to expand...

I finally got around to running 4 instances of the analysis app. Once all were loaded and running, they each were using in excess of 12GB of RAM. I left it for a while without noticing any change. Then I started loading other apps and when the total computers memory usage reached about 90%, each analysis instance reduced their memory usage to just over 4GB. Now that is exactly where their usage is staying, even after unloading the other apps - WAHOO!

It appears that DotNet only flushes unused memory when the system's memory starts to get near full.

Thanks for all your help. I really appreciate it.

Regards

Alan :)
 
Upvote 0 Downvote

Similar threads

RobertAlanGustafsonII
  • Question
Question How do I perform an asynchronous file save which for events that track progress and completion?
Replies
2
Views
1K
RobertAlanGustafsonII
RobertAlanGustafsonII
Z
  • Question
Not Sure How To Do A Control Array Or Pass An Object To A Sub
Replies
3
Views
1K
jdelano
J
M
  • Question
Question Help. Can't assign an array of images to an ImageList.
2
Replies
18
Views
3K
Mugsy
M
RobertAlanGustafsonII
  • Solved
Question How do I determine when Skype for Desktop is "ready" to process an argument-supplied action?
Replies
7
Views
1K
jmcilhinney
jmcilhinney
UmerMahir
  • Question
refrence not set to an instance of an object
Replies
3
Views
3K
UmerMahir
UmerMahir

Share this page

Latest posts

Back
Top