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

  • 1
    Could you provide your overall code or example close to it? – thechrishaddad Aug 3 '17 at 0:50

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:

 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 (
    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.
  • Why are you using the ` isnull(sl.JobID,0) JobID` ? – Samuel Dare Aug 3 '17 at 11:55
  • There were null values and it's part of the primary key on the target DE. – Adam Spriggs Aug 3 '17 at 11:59
  • I completely agree with your answer, it states many ways to improve sql performance, especially when you reduce joins on data views I find that a major advantage or sometimes I break up the queries depending on the scenario. – thechrishaddad Aug 3 '17 at 12:18

I'm told that you can request Marketing Cloud Support to index specific fields in a specific Data Extension, which may help you here, or it may not.

The real issue is that Marketing Cloud simply isn't designed to process hundreds of millions of records. If it was, you wouldn't be running into this problem in the first place.

I have a client with 160 million records in a Data Extension and I experienced a similar issue this week, even when I used a simple query to retrieve two fields:

SELECT Id, PurchaseDate
FROM Transactions

... this query also timed out. My recommendation to the client (and any client) is not to attempt to perform ETL (Extract-Transform-Load) and segmentation processes for very large data sets in Marketing Cloud, as you will just end up in a world of pain, which does not stop at query timeout issues. This isn't limited to Marketing Cloud either, coping with large data sets is also an issue in Sales Cloud.

For customers that need large data sets, I recommend they perform ETL and data segmentation outside of Marketing Cloud and just import the data that's required, which can be achieved using a platform like Stride, which integrates tightly with Marketing Cloud as I have explained in this article.

  • Good luck getting a SFMC Data Architect to add an index. I've seen it done once and it took months. SFMC handle hundreds of millions of rows for some business units and choke and DEs a fraction of the size in others. The issue is the platform and the tuning. In my experience, dumping data out to something else isn't normally cost effective for clients. – Adam Spriggs Aug 3 '17 at 11:57

The problem here is that T-SQL lacks the functionality of indexing, foreign keys are not specifically defined as a foreign key type either in T-SQL so you end up with not being able to index foreign keys in order to speed up performance like more advanced SQL engines.

Very time consuming overall considering you have 400M records that would easily timeout with a few joins and the 30 minute timeout period in Marketing cloud.

This will help but not best practise unless it is your only option:

Due to the lack of SQL functionality try using the NOLOCK SQL function, this will ignore locking each record during execution and hopefully speed up performance.

ON a.Id = b.Id

I do not recommend using this approach unless it is mandatory and justified as in your case, the records are quite large and in need of performance improvement without the ability to index foreign keys.

Hope this provides you some guidance, here is some information on NOLOCK

NOTE: Do not take this approach for every lack of performance you are experiencing, it is not the easy way out but some times it is your only way out.

  • :( thanks but already using no locks – Samuel Dare Aug 3 '17 at 0:05
  • And the funny thing is I just had a query run successfully for over an hour?!? – Samuel Dare Aug 3 '17 at 0:06
  • Ah okay, You don't have much code examples listed so hard to advise and there was no mention of nolock in your question. Yeah it will definitely run, it will just take a hell of a long time. – thechrishaddad Aug 3 '17 at 0:08
  • Sorry will update. So is the 30 mins not a hard time out? – Samuel Dare Aug 3 '17 at 0:10
  • 1
    The 30 minutes is a forced timeout as they assume each query in your automation should not exceed 30 minutes. – thechrishaddad Aug 3 '17 at 0:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.