New post
Question
· Dec 17, 2024

Production pooled component index

#Business Process (BPL) #SQL #InterSystems IRIS for Health

I have a business service which is responsible for some batch operations with an SQL table. The process is generally slow but it is possible to scale the performance using multithreading and/or parallel processing and logical partitioning (postgres):


select id, col1, col2, mod(row_number() over (), 4) as partition from some_table;

Thus, a partition index will be assigned to each table row. The idea is to create several instances of my business service using pooling (e.g. Pool Size = 4) so each business service instance will be responsible to hande rows belonging to a certain partition, e.g.:

#Dim partition
Set partition = ..FindPoolIndex($this)
#Dim sql 
Set sql = "with tmp as (select id, col1, col2, mod(row_number() over (), 4) as partition from some_table) select * from tmp where partition = " _ partition

Is it possible to somehow programmatically find out the index of a pooled component in a pool?

Product version: IRIS 2024.3
Discussion (5)1
Log in or sign up to continue
Toni Crooz · Dec 17, 2024

Yes, you can programmatically determine the index of a pooled component in InterSystems IRIS by using the FindPoolIndex method, which will return the index of the current component in the pool. This method can help you identify which partition of the data each business service instance should handle, allowing you to assign the correct partition in your SQL query. Ensure that the FindPoolIndex method is properly implemented to map the instance to the correct partition index, as shown in your example. This will allow you to scale the batch operations effectively using pooling and partitioning. 🚀

0 0
Dmitrii Baranov · Feb 25

Here it is (implemented as Mixin):

Class MyNamespace.Pooled Extends Ens.Host [ Abstract ]
{

Property PoolIndex As %Integer [ Calculated ];

Method PoolIndexGet() As %Integer
{
    #Dim cn as %String
    Set cn = ..%ConfigName  

    #Dim statement as %SQL.Statement
    Set statement = ##class(%SQL.Statement).%New()
    Set status = statement.%PrepareClassQuery("Ens.Job","Enumerate")
    $$$ThrowOnError(status)

    #Dim rs as %SQL.StatementResult
    Set rs = statement.%Execute()
    #Dim i as %Integer = -1

    While (rs.%Next()) 
    {
        #Dim jobId as %String 
        Set jobId = rs.%Get("Job")
        If (rs.%Get("ConfigName") = cn)
        {
            Set i = i + 1
            If (jobId = $JOB) 
            {
                Kill rs
                Return i
            }
        }
    }
    Kill rs
    Return i
}

Property PoolSize As %Integer [Calculated];

Method PoolSizeGet() As %Integer
{
    #Dim cn as %String
    Set cn = ..%ConfigName
    #Dim statement as %SQL.Statement
    Set statement = ##class(%SQL.Statement).%New()
    Set status = statement.%PrepareClassQuery("Ens.Job","Enumerate")
    $$$ThrowOnError(status)

    #Dim rs as %SQL.StatementResult
    Set rs = statement.%Execute()
    #Dim i as %Integer = 0

    While (rs.%Next()) 
    {
        If (rs.%Get("ConfigName") = cn)
        {
            Set i = i + 1
        }
    }
    Kill rs
    Return i
}

}
0 0