Written by

UKK Köln

Question Dmitrii Baranov · Dec 17, 2024

Production pooled component index

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

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 (6)2

Add reply

Comments

Stephen Canzano · Dec 17, 2024

which is responsible for some batch operations with an SQL table

Is this a native IRIS SQL Table and have you examined the Query plan for the SQL Table?

0 0

Dmitrii Baranov Dec 17, 2024 to Stephen Canzano

No, it is a Postgres linked table, but it is not really important, I just want to parallelize the process

0 0

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 Dec 17, 2024 to Toni Crooz

`..FindPoolIndex` doesn't exist, it's pseudocode (by the way, ChatGPT has very little and poor knowledge about IRIS)

0 0

Dmitrii Baranov · Feb 25, 2025

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

blacka rrow Mar 31, 2025 to Dmitrii Baranov

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 run 3 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
}

}

Your Pooled class in InterSystems IRIS or Caché ObjectScript efficiently determines the pool index and pool size for a given configuration.

Let us know how it goes. Looking forward to helping you!

0 0