B4J Question Connection Pool Management

[incendio]

Hi guys,

I need an app that periodically check sql table rows and do some operations based on its values.

I have two codes to do that.

Option A
Open connection once and never close it and used it again when necessary.

Sub Process_Globals
    Private Pool1 As ConnectionPool
    Private Pool2 As ConnectionPool
    Private Tm As Timer
    Private Qry1,Qry2,ExecQ1,ExecQ2 as SQL
end Sub

Sub AppStart (Args() As String)
    Pool1.Initialize(...)
    Pool2.Initialize(...)
    
    Qry1 = Pool1.GetConnection
    Qry2 = Pool2.GetConnection
    ExecQ1 = Pool1.GetConnection
    ExecQ2 = Pool2.GetConnection
    
    Tm.Initialize("TM",3000)
    Tm.Enabled = True   
    StartMessageLoop   
End Sub

Sub TM_Tick
    TM.Enabled = False
    Private RS As ResultSet
    RS = Qry1.ExecQuery("select * from table where log = 0 order by id")
    Try
        ExecQ1.BeginTransaction
        ExecQ2.BeginTransaction
        Do While RS.NextRow
            'do some operations'
        Loop
        ExecQ1.TransactionSuccessful
        ExecQ2.TransactionSuccessful
    Catch
        ExecQ1.Rollback
        ExecQ2.Rollback
    End Try
    RS.Close
    TM.Enabled = True
End Sub

Option B
Open connection before used & close connection after used.

Sub Process_Globals
    Private Pool1 As ConnectionPool
    Private Pool2 As ConnectionPool
    Private Tm As Timer
end Sub

Sub AppStart (Args() As String)
    Pool1.Initialize(...)
    Pool2.Initialize(...)
    
    Tm.Initialize("TM",3000)
    Tm.Enabled = True   
    StartMessageLoop   
End Sub

Sub TM_Tick
    TM.Enabled = False
    Private Qry1,Qry2,ExecQ1,ExecQ2 as SQL

    Qry1 = Pool1.GetConnection
    Qry2 = Pool2.GetConnection
    ExecQ1 = Pool1.GetConnection
    ExecQ2 = Pool2.GetConnection

    Private RS As ResultSet
    RS = Qry1.ExecQuery("select * from table where log = 0 order by id")
    Try
        ExecQ1.BeginTransaction
        ExecQ2.BeginTransaction
        Do While RS.NextRow
            'do some operations'
        Loop
        ExecQ1.TransactionSuccessful
        ExecQ2.TransactionSuccessful
    Catch
        ExecQ1.Rollback
        ExecQ2.Rollback
    End Try
    RS.Close
    Qry1.Close
    Qry2.Close
    ExecQ1.Close
    ExecQ2.Close
    TM.Enabled = True
End Sub

Which option is better in performance and RAM usage?

 

[Jmu5667]

When using the connection pool you should always close a connection when you have finished the work.

 

[incendio]

When using the connection pool you should always close a connection when you have finished the work.

So option B is better. Why?

Forgot to mention that the application runs only for one user.

 

[Erel]

Why are you getting multiple connections from the pool? Looks wrong.

So option B is better. Why?

The pool manages the connections. It takes care of providing you a working connection.

If you take a connection and keep it open then the pool doesn't do anything for you and you will need to manage the connection state and always check whether it is active or not. In that case there is no reason to use a pool.