SQL Query Timeout Help SFMC

Question

I have the following query that is consistently timing out for the last few weeks. It had ran perfectly fine since 2019, previously. I know the query needs to be optimized, but I don't see anything egregious besides maybe changing the subkey check. I did a TOP 50 PERCENT and even 25 Percent, all timed out. Query without the top is

select s.subscriberkey as Salesforce_ID,CAST (MAX(s.EventDate) AS DATE) as Last_Marketing_Email_Sent 
from 
_Sent s with(nolock)
inner join [DE] a on a.Salesforce_ID=s.subscriberkey
inner join _Job j with(nolock) on s.jobid = j.jobid
where  j.sendclassificationtype = 'Default Commercial' and  (s.SubscriberKey NOT LIKE '%-%') and  (s.SubscriberKey NOT LIKE '%@%') and EventDate >= DATEADD(dd, -30, CURRENT_TIMESTAMP) 
Group by s.subscriberkey

Answer 1

There are multiple options that can help to prevent time-outs. SQLs time out after 30 minutes. I will start with the most important ones.

1. Do not process data that you don’t need. Remove any useless attributes which are slowing down the processing. Avoid using overwrite action if possible and instead use update action.

2. You should create a map or a timeframe of all heavy backend processes running on your instance. Imports, Data Processing, etc. and try not to have them running concurrently if possible. Reschedule it if possible. Instead of running on an hourly basis, run every two hours or less often if possible. Once there are a lot of automations running at the same time, the database gets busy and it takes longer for your SQLs to finish.

3. Approach that you described, but there are 2 ways to do it.

• Using SELECT TOP, you can split the source data into two parts (TOP 51% ASC using overwrite AND TOP 50% DESC using update, 51% overwrite and 50 % update not to lose records in the middle)

• Via a so-called mob-based approach using an invisible system field _customobjectKey. This way you can create more than 2 subsets (4, 8 etc.). For 4 splits use the following WHERE statements

  • SQL 1, step no. x, action overwrite: WHERE _customobjectKey % 4 = 0
  • SQL 2, step no. x+1, action update: WHERE _customobjectKey % 4 = 1
  • SQL 3, step no. x+2, action update: WHERE _customobjectKey % 4 = 2
  • SQL 4, step no. x+3, action update: WHERE _customobjectKey % 4 = 3

4. Filters in Marketing Cloud do not time out, however, you cannot create complex filters with them, so I am not sure if this would help with some of your queries.

5. If you have not purchased a dedicated database from salesforce yet, your database is most likely shared with another client(s) that you are not aware of. You cannot really do anything about optimizing this on your side. Purchasing a dedicated database will help, however, even in this case, you can end up with timeouts if you have multiple business units, hundreds of automations, and tens or hundreds of millions of records being processed at the same time. You might have to optimize that as well.