Email pressure rule - too long execution

Avatar

Avatar

Milan_Vucetic

MVP

Avatar

Milan_Vucetic

MVP

Milan_Vucetic
MVP

25-09-2019

HI all,

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.

Any idea?

1837517_pastedImage_1.png

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)))))

Regards,

Milan

View Entire Topic

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

27-09-2019

Yes it is weird.

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.

When I do the same with my Adobe Campaign instance (with underlying SQL Server RDBMS engine, but it should not be so different), the Query executed from a workflow, either in a Javascript activity or reproducing the same with a Query activity (so underlying queryDef generated instruction), it lasts long as well.

logInfo("[WKF17616]start SQL query with cnx.query in Javascript activity");

var cnx = application.getConnection()

try

{

  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);              

}

catch(e)

{

  logWarning("[WKF17616] query catched error");

  logWarning("Error number: " + e );

}

cnx.dispose();

logInfo("[WKF17616] SQL query ith cnx.query in Javascript activity completed");

Regards
J-Serge