ERROR: Query failed: Timeout SFMC

Question

Anyone knows what is the cause of the error in my Stuff SQL? i run the query many times and keep receiving this error. This query will get all the ProductCode based on every customer and i want to display in into data extension.

Here's my query:

SELECT DISTINCT b2.ContactId,

ProductCode = STUFF(
(
SELECT ',' + pcode.[ProductCode]

FROM
Asset_Salesforce as asset

LEFT JOIN Product2_Salesforce as pcode
ON pcode.Id =asset.Product2Id

LEFT JOIN Contact_Salesforce as con
ON asset.ContactId = con.Id

WHERE
asset.ContactId = con.Id
AND
pcode.Id = asset.Product2Id

FOR XML PATH ('')
), 1, 1, '')
FROM [Asset_Salesforce] as asset2

LEFT JOIN [Customer Data Layer_v2] as b2
ON b2.AccountId = asset2.AccountId
WHERE b2.AccountId != ''

GROUP BY b2.ContactId, asset2.Id

Answer 1

The reason for your timeout error is you are running an expensive query that is timing out after 30 minutes. Salesforce has a useful page on optimisation of query activities. Many of the points I think you'll find useful in tuning the query above, most notably the use of intermediary/staging tables and joining on Primary Key fields. Also consider the need for expensive operations like DISTINCT and limiting result sets by using INNER, rather than OUTER joins.

Without having your data to hand or knowing too much about the business rules you're looking to apply, it's a little hard to write hypothetical SQL, here, but here's an example of breaking down your automation.

1. Overwrite ContactsWithProducts (PK = ContactId,ProductCode)

SELECT 
    a.ContactId AS ContactId,
    p2.Id AS ProductCode    
FROM
    Product2_Salesforce p2 INNER JOIN 
    Asset_Salesforce a
        ON  p2.Id = a.Product2Id

2. Overwrite DataLayerContacts (No PK)

SELECT 
    c.Id AS ContactId,
    cdl.AccountId 
FROM
    Contact_Salesforce c INNER JOIN 
    [Customer Data Layer_v2] cdl
        ON  c.AccountId = cdl.AccountId

3. Overwrite MyResultsTable (No PK)

SELECT 
    dlc.ContactId,
    STUFF(
        (
        SELECT  ',' + cwp.ProductCode
        FROM    ContactsWithProducts cwp
        WHERE   cwp.ContactId = dlc.ContactId
        FOR XML PATH('')
        )
    ,1,1,'') AS ProductCodes
FROM
    DataLayerContacts dlc