0

I have a Data Extension with over 430.000.000 records and I'm trying to do a basic select query but due to the number of records , it is timing out.

What options do you think I have?

SELECT
CustomerID(pk), 
iv.sku,
iv.user_id,
iv.session_id

FROM [PRS] P


JOIN [PRS_PROFILES] pp ON
p.user_id = pp.user_id

JOIN [Radar] r ON
pp.email = r.CustomerID
1

Not knowing your query, it is difficult to see the potential for optimisation. I.e. are you joining multiple DEs in your query?

Querying on primary key is most efficient, as this field automatically becomes indexed. e.g. SELECT * FROM Customers where custID = 1234 (CustID being your primary key)

In some cases you can request an index on a field not being a primary key. Reach out to Salesforce support to request this

  • Sorry, I have updated my question with the query – Duicug Aug 29 at 14:28
1

Marketing Cloud uses Microsoft SQL Server which has a row limit of 8,060 bytes. Columns that don't fit are placed off-row, in a separate internal table and when you run a query on the Data Extension, the platform has to execute multiple queries, which can result in a time-out.

What you can definitely do, is reduce the number of columns and make sure you don't have unnecessary overhead when it comes to field length. Also only import the data you need and always assign primary keys or composite keys.

If would be beneficial if you could provide the Data Extension structure and the query that you are trying to run.

You can read more about query best practices here: Optimize SQL Query Activity performance in Marketing Cloud

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.