Details
Major Description
In Statements.java, we have the following query:
public String getFindNextMessagesStatement() { if (findNextMessagesStatement == null) { findNextMessagesStatement = "SELECT ID, MSG FROM " + getFullMessageTableName() + " WHERE CONTAINER=? AND ID < ? AND ID > ? AND XID IS NULL ORDER BY ID"; } return findNextMessagesStatement; }
This statement is used in DefaultJDBCAdapter in the doRecoverNextMessages().
In this method, we do:
s.setMaxRows(Math.min(maxReturned, maxRows));
setMaxRows is supposed to limit the number of rows. However, it doesn't seem to prevent the concrete SQL executed on the database side.
It means that we have a huge huge impact on performance (especially with lot of pending messages).
To avoid this performance issue, the findNextMessagesStatement select statement should use limit (with the same value of setMaxRows).
As it's more "postgresql" specific (any database supporting limit should do the same), we can update PostgreSQLJDBCAdapter to use limit.
It's not yet fully clear to me if the issue if about the PostgreSQL JDBC driver (bad "translation" of the setMaxRows()) or just the ActiveMQ select statement should use limit.
