Query Failing: Time Out

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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.

SELECT * FROM t1 a WITH (NOLOCK)
INNER JOIN t2 WITH (NOLOCK) b 
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.