Hi Vitaly and thanks for the idea.
The table contains 3.5M rows

create table test as select row_status from <src>
create bitmap index test_row_status_idx on test(row_status)

select top 10 ID from test where row_status in ('U','I') order by ID

0.579s

So most probably you are right and the table indeed has some issues with indexes

Done. Neither `collect statistics` nor `tune table` help. 

By the way, how normal is this? Index usage = 0 everywhere

Two times :( First this index, then the whole table too. Then I recreated the index as bitmap, rebuilt all indexes again - doesn't work

If it is important, the index was created automatically by the Data Migration Wizard.
In the list of indexes, formerly it had type 'index'.
I've recreated the index, this time explictly as 'bitmap':

create bitmap index <name> on <schema>.<table>(row_status) 

Now I see that it has type 'bitmap'.
Unfortunately, that did not work, and the plan hardly differs:

SELECT TOP ? ID FROM <table>.<schema> WHERE row_status IN ( ? , ? ) ORDER BY ID /*#OPTIONS {"DynamicSQLTypeList":"10,1,1"} */
Query Plan

Relative Cost = 105.04
• Read extent bitmap  <table>.<schema>.%%DDLBEIndex, looping on ID.
• For each row:
    - Read master map  <table>.<schema>.IDKEY, using the given idkey value.
    - Test the IN condition on %SQLUPPER(row_status) and the NOT NULL condition on %SQLUPPER(row_status).
    - Output the row.

three more notes (without using the concatenation like `order by ID || ''`):

• row_status in ('I', 'U') order by ID - hangs
• (row_status = 'I') or (row_status = 'U') order by ID - hangs
• (row_status = 'I') order by ID - fast

This plan is for the slow (to be more precise, infinite) one. The "fast" one (that uses the concatenation trick) looks like:

SELECT TOP ? ID FROM <schema>.<table> WHERE row_status IN ( ? , ? ) ORDER BY ID || '' /*#OPTIONS {"DynamicSQLTypeList":"10,1,1"} */ /*#OPTIONS {"rtpc-utility":1} */ /*#OPTIONS {"rtpc-log-sel":["1^.0390625"]} */
Warning

• Complex condition TOP on ID from table <schema>.<table> will not filter out any rows.
Information

• This query plan was selected based on the runtime parameter values that led to:
    Using the outlier selectivity in an IN condition on row_status.
Query Plan

Relative Cost = 620185
• Call module B, which populates temp-file A.
• Read temp-file A, looping on the '_' expression and ID.
• For each row:
    - Output the row.
Module: B

• Read index map <schema>.<table>.row_status_idx, looping on %SQLUPPER(row_status) (with a given set of values) and ID (with a TOP condition).
• For each row:
    - Add a row to temp-file A, subscripted by the '_' expression and ID,
        with no node data.

Hi Nick,

1. Yes I do (double checked)
2. The plan is:

SELECT TOP ? ID FROM <schema> . <table> WHERE row_status IN ( ? , ? ) ORDER BY ID /*#OPTIONS {"DynamicSQLTypeList":"10,1,1"} */
Query Plan

Relative Cost = 105.04
• Read extent bitmap <schema>.<table>.%%DDLBEIndex, looping on ID.
• For each row:
    - Read master map <schema>.<table>.IDKEY, using the given idkey value.
    - Test the IN condition on %SQLUPPER(row_status) and the NOT NULL condition on %SQLUPPER(row_status).
    - Output the row.

Thanks Vitaly, it looks like a magic spell but it works.

I'm curious why LPAD, RPAD and similar string functions are not exposed as $SYSTEM.SQL.Functions class members

Thanks a lot Cecilia, now I can continue learning HealthShare 😊

https://containers.intersystems.com/contents/containers

No, JVM settings are not the reason.

It seems to me the reason is the way how the XDBC Java wrapper is implemented. There are two things I don't really like about the code:

1. Statements created by an instance of XDBC Connection objects are being put in a List which keeps growing and growing. Even if I call Statement.close(), it will not be removed from the list immediately. The list is cleared only when the Connection.close() method is called.

2. Less likely, but this could also be the cause of the problem: no ResultSet.close() calls. The specification says:

https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#close()

It is strongly recommended that an application explicitly commits or rolls back an active transaction prior to calling the close method. If the close method is called and there is an active transaction, the results are implementation-defined.

Modern JDBC drivers try to close dependent objects cascadely on Connection.close() but it is not always the case, and it is recommended to always close them explicitly.

In particular, someone complained about the similar issue with the Postgres JDBC driver here. And in the XDBC code I see that it tries to close and release all Statement instances but not ResultSets. On the other hand, Postgres driver maintainers reported that namely this issue was fixed.

1. It is officially declared that XDBC is the most modern remote data access technology

2. I use remote data sources very extensively and have tried everything - linked tables, Ens.* business operations and adapters, foreign tables, and XDBC. XDBC looks like the most simple and attractive data access technology. Using foreign servers and tables you need to describe both the server and the tables. Using XDBC you work with any ODBC or JDBC data source transparently.
In particular, I have had bad experience with both Postgres linked tables and with Postgres foreign tables, especially when remote/Postgres tables contain columns of type `text` or `bytea`. In this case, IRIS often silently returns an empty dataset without any errors even if a remote table is actually not empty.

Batch updates are indeed supported by XDBC but I need to execute updates in real time without any delay, row-by-row. 

Hi Enrico,

1. I'm aware of that warning "FOR INTERNAL USE" in the source code, but the way I create XDBC connections is officially documented

2. The OS is Ubuntu, IRIS is containerized, the memory is consumed by the IRIS Java language server

3. Regarding memory leaks, I suspect that ResultSets are not closed properly - method %XDBC.Gateway.JDBC.ResultSet::Close() is empty and does nothing but it seems to be it should call ..%externalResultSet.close(). But I'm not sure it is the only reason

4. UPD: calling Java GC really helps:

Set gateway = $system.external.getGateway("%Java Server")
Do gateway.invoke("java.lang.System", "gc")

```