there is an issue with an email pressure rule which takes to long to execute. Interesting thing is that takes around ~1,5 hour for 10.000 customers and also 1.5 hour for only 3 customers. There is no locks on DB or similar things. Below is the configuration, it just takes too long. Optimizing SQL query does not help.
SELECT R0.iRecipientId FROM NmsRecipient R0 WHERE EXISTS (SELECT 1 FROM NmsBroadLogRcp B1 WHERE (B1.iRecipientId = R0.iRecipientId) AND (((B1.iStatus NOT IN (2 , 7)) AND ((((case when IsBitSet(B1.iFlags , 😎 = 8 then 1 else 0 end) = 0) AND (B1.iStatus <> 0)) OR ((case when IsBitSet(B1.iFlags , 😎 = 8 then 1 else 0 end) = 1)))))
But with SQL Developer, Oracle underlying RDBMS could perhaps manage the query differently.
Please run the query within a workflow, not through the typology / delivery in order to check.
From my side the query lasts very long, because I thing the EXISTS subquery is ran over the whole database.
I think it is the same with the delivery/typology rule, the EXISTS subquery is executed for all your database, that explains the constant response time, whatever the delivery target contacts count.
var cnx = application.getConnection()
var reqSql = "SELECT R0.iRecipientId " +
"FROM NmsRecipient R0 " +
"WHERE EXISTS " +
"(SELECT 1 " +
"FROM NmsBroadLogRcp B1 " +
"WHERE (B1.iRecipientId = R0.iRecipientId) " +
"AND (((B1.iStatus NOT IN (2 , 7)) " +
"AND ((((case when $(pfx)IsBitSet(B1.iFlags , 😎 = 8 then 1 else 0 end) = 0) " +
"AND (B1.iStatus <> 0)) " +
"OR ((case when $(pfx)IsBitSet(B1.iFlags , 😎 = 8 then 1 else 0 end) = 1)))) " +
var stmt = cnx.query(reqSql);
logWarning("[WKF17616] query catched error");
logWarning("Error number: " + e );