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

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

02-10-2019

Hi Milan,

I guess this is the choice of storage compacity, using a byte @flags (sqlname iFlags) and OR logic to store 3 boolean values (controlGroupMember, quarantine, seedMember) as bits in this huge volume table, versus the efficiency/performance type for searches on these 3 booleans.
As you can see it in the source schema nms:broadLog, the 3 booleans are calculated fields (dynamic), not stored in the table.

I don't know exactly your business case and what you do, but I see 2 solutions: adding an extra field equivalent to controlGroupMember, and perhaps an index on it, resulting in table a bit larger, and perhaps drawback in performance somewhere in some realtime processes.
Or filling the value in batch processes.

Well, you would rather analyse your business case, perhaps there is another idea to find out instead of schema customization.

Regards
J-Serge

Answers (5)

Answers (5)

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

27-09-2019

Hi Milan,

So my previous post explained the constant response time behavior.

Then, the query lasts for you 1,5 hour.
May you give us an hint, how many nms:broadLogRcp records do you have in your instance?

As comparison, for 140M of records (lines), the SQL query lasts 10 minutes with the wkf JS cnx.query method given in my previous post. And almost the same with the Query activity.

Regards
J-Serge

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

Avatar

Avatar

Jean-Serge_Biro

MVP

Avatar

Jean-Serge_Biro

MVP

Jean-Serge_Biro
MVP

26-09-2019

Hi Milan,

Please may you confirm that the query executes quickly when you run it from a dedicated targeting workflow?

Regards
J-Serge

Avatar

Avatar

Milan_Vucetic

MVP

Avatar

Milan_Vucetic

MVP

Milan_Vucetic
MVP

29-09-2019

Hi J-Serge,

this is just awesome answer. Well, mentioned JS cnx.query lasts 22 min 39 seconds on my instance. Regarding Broadlog table, it has ~264.000.000 records.

Therefore, why I have execution for 1,5 hour? If you are sure this is caused by expensive query, is there a better way to optimize it?

Regards,

Milan

Avatar

Avatar

Milan_Vucetic

MVP

Avatar

Milan_Vucetic

MVP

Milan_Vucetic
MVP

26-09-2019

Hi J-Serge,

I tried to execute SQL mentioned above from SQL Developer and it runs around ~10 minutes. Therefore, added one more condition to where clause to look Broadlog table for last 10 day only (as rule look last 5 day) and query execution decreased from 10 minutes to 2 minutes in SQL Developer, then put that query in AC and that step in AC still takes around 1 hour 30 minutes. It is really weird that processing 1 and 10K customers for the same time.

Regards,

Milan