B4J Question Resultset not clean by GC after closing.

[Swissmade]

I have a small issue.
Playing with VisualVM and create a heapdump, I see the resultset is not clean by GC even after closing.
Memory usage go slow up.
Null the Resultset is helping some but I think this is not the way to do it.

Any idea about this?

Sample Sub
Private Sub StartQuery(strMYSQL As String, Task As Int)
    Try
        strMYSQL = strMYSQL.Trim
        Dim strSelectPart As String = "SELECT " & Task & " AS QueryID, "
        If strMYSQL.Contains("SELECT") Then
            strMYSQL = strMYSQL.SubString("SELECT".Length + 1)
            strMYSQL = strSelectPart & strMYSQL
'        strMYSQL = strMYSQL.Replace("SELECT", "SELECT " & Task & " AS QueryID, ")
        End If
        If strMYSQL.Contains("COUNT") = False And strMYSQL.Contains("SELECT") = True Then
            Functions.LastQuery = strMYSQL 'Check for Select Query
        End If
        If Functions.SQLASYNC.IsInitialized = False Then
            Functions.ConnectToSQLASYNC
        End If
        '***********************************************************************************
        Functions.WriteQUERYLog("In startQuery " & CRLF & strMYSQL)
'        Functions.SQLASYNC.ExecQueryAsync("SQLLOG", strMYSQL, Null)

        Dim SenderFilter As Object = Functions.SQLASYNC.ExecQueryAsync("SQLTMP", strMYSQL, Null)
        Wait For (SenderFilter) SQLTMP_QueryComplete (Success As Boolean, RS As ResultSet)
        If Success = True Then
            Wait For (SQLLOG_End(Success, RS)) Complete (Retvalue As Boolean)
        Else
            Log(LastException)
        End If
        '***********************************************************************************
    Catch
'    lblMess.Text = "Last error in StartQuery " & LastException.Message
        Functions.ShowLog("Last error in StartQuery Main", True, False, LastException.Message)
    End Try
    If RS.IsInitialized = True Then
        RS.Close
        RS = Null
    End If
    '****************************************************
    If Functions.SQLASYNC.IsInitialized = True Then
        Functions.SQLASYNC.Close
    End If
End Sub

 

[LucaMs]

Any idea about this?

You could empty the ResultSet before closing it; for example by running: "Select '' "

 

[Swissmade]

You could empty the ResultSet before closing it; for example by running: "Select '' "

Hm is that the way to do it. I was thinking about when I have a Local Resultset it can be closed.

But will check your option.
Thanks

 

[Swissmade]

What I do for testing is maybe that's the way to do it then.

    If RS.IsInitialized = True Then
        RS.Close
        Dim RS As ResultSet
    End If

 

[Swissmade]

I always was thinking that the Objects out of scope and will be cleaned by the GC when they are in a local Sub.
Resultsets and connection(SQL) I always close. before the Sub ends.

 

[Swissmade]

Many times I use the Resultset like this and GC can not clean it.

         Dim rs As ResultSet = sql1.ExecQuery("SELECT table_name FROM information_schema.tables")
         Do While rs.NextRow
              Log(rs.GetString2(0))
         Loop
         rs.Close

 

[Erel]

1. Unless it is a global variable then there is never a reason to "null" it.
2. All you need to do is to close the ResultSet.

I'm running many servers and some of them run for years. There is no memory leak in ResultSet if used correctly.

Let your app run for several hours and see if it actually runs out of memory. If so then you have a memory leak and need to dig deeper.

 

[Swissmade]

I have let the App run for hours and memory usage is slowly go up what I can see in VisualVM

I always close the resultsets.
Close the RS where it is coming from like

Dim RES as Resultset

RES = QueryToExecute
Wait For (Test(Res)) Complete ( Retvalue as Boolean)
RES.Close

Private Sub Test(RS as resultset) As ResumableSub
<code here to handle the Resultset Data>
Return True
End Sub

 

[Erel]

I don't understand this pattern. Why aren't you using ExecQueryAsync? Why is Test a resumable sub?

And the fact that memory grows slowly doesn't mean too much unless you actually hit an OOM.

 

[Swissmade]

I don't understand this pattern. Why aren't you using ExecQueryAsync? Why is Test a resumable sub?

And the fact that memory grows slowly doesn't mean too much unless you actually hit an OOM.

This sub was just a sample not real.
So you mean use all SQL commands as ExecQueryAsync where I fill a Resultset

Something like this?

        Dim SenderFilter As Object = Functions.SQLASYNC.ExecQueryAsync("SQLTMP", strMYSQL, Null)
        Wait For (SenderFilter) SQLTMP_QueryComplete (Success As Boolean, RS As ResultSet)
        If Success = True Then
            Wait For (SQLLOG_End(Success, RS)) Complete (Retvalue As Boolean)
        Else
            Log(LastException)
        End If

Then use the Resultset RS after that just close or give it Null?

 

[Erel]

or give it Null?

I already wrote - unless it is a global variable there is never a good reason to null variables.

Something like this?

No. Why is SQLLog_End a resumable sub?

 

[Swissmade]

I already wrote - unless it is a global variable there is never a good reason to null variables.


No. Why is SQLLog_End a resumable sub?

I was thinking it is waiting until the RS is handled and then come back to process the rest of the code.
And Null variable no need then in Sub. Thanks

 

[Swissmade]

Hi Erel,
Many thanks for this hint ExecQueryAsync.
It is the solution to fix the Memory issue.
Lot to do in this application to but this to all Resultsets.
Never to old to learn.
Many thanks again.