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