0

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.

enter image description here

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
3
  • The timeout error's fairly self-explanatory. You're running a very expensive query and it's resulting in a timeout after 30 mins. You'd need to do some tuning. If you're not wanting to know about products with no asset or assets with no contact, why do an outer join? If you don't need Account_Salesforce fields in your output, why join to it? You have the account Id already available in your Contact_Salesforce data. If you're grouping by ContactID and AssetId, what's the point of using a DISTINCT? – Macca Apr 3 '20 at 0:51
  • Try breaking your query down into multiple less expensive queries. For example, dump your contacts with assets into a staging table, then try doing your expensive STUFF and XML PATH operation in separate queries using smaller subsets of data. – Macca Apr 3 '20 at 0:51
  • 1
    Consider the Primary Keys on your staging DE's. You can force the creation of indexes to help with performance. – Macca Apr 3 '20 at 0:56
2

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
3
  • Hi @Macca thank you for the detailed explanation. i wanted to simplify my query and limit the result by 2 productCode per Contact. can you help me ? – JGB Apr 3 '20 at 4:34
  • the only goal is i need to merge the ProductCode in each rows pero ContactId – JGB Apr 3 '20 at 4:36
  • So, you eventually want a table containing two fields - "ContactId" and "ProductCodes" (comma delimited list)? – Macca Apr 3 '20 at 5:03

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.