- Mark as New
- Follow
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report
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