[SalesForce] Query Failing: Time Out

I have a query that has started failing due to the fact that it has over 400M rows. Although there are joins and fuzzy matching (LIKE '%XXXX%') it is still failing. Using NO LOCK too
I have used staging tables to group the joins and likes but but the real bottle neck is the 400 M table.

I have thought of breaking the table down and joining them back but not sure if this isnt even a worse way to approach this.

I will appreciate guidance on this

Best Answer

There is a hidden, indexed field in every data called _customObjectKey and it's fast. While it seems counter-intuitive, you can leverage it in your queries that are timing out by adding an additional join:

select
 isnull(sl.JobID,0) JobID
, isnull(sl.ListID,0) ListID
, isnull(sl.BatchID,0) BatchID
, isnull(sl.SubID,0) SubID
, isnull(sl.TriggeredSendID,'') TriggeredSendID
, isnull(sl.ErrorCode,0) ErrorCode
, isnull(sl.emailAddress,'') emailaddress
, isnull(sl.CampaignName,'') CampaignName
from [SendLog Data Extension] sl
inner join (
    select
    min(_customobjectkey) n
    , max(_customobjectkey) x
    from [SendLog Data Extension]
    where sendDate >= convert(date, getdate()-1)
    and sendDate  < convert(date, getdate())
) a on sl._customobjectkey between a.n and a.x
/* name: SendLog Data Extension Recent */
/* target: SendLog Data Extension Recent */
/* action: overwrite */

NOTE: The _customObjectKey may not be sequential if the DE has been updated with another query.

There are some other things that I've outlined in a post on my Troubleshooting Queries in SFMC blog post:

  1. Reduce the number of rows
  2. Leverage primary keys
  3. Reduce the number of JOINs, especially ones to the System Data Views
  4. Make sure your conditions are sargable.
Related Topic