2

We're working with somewhat large sets of data, around 15M - 35M records per DE, each row with 150+ attributes.

What are some ways to optimize SQL queries when handling many records?

  • For an exaple, using intermediate DE's and SQL queries selecting TOP 50% from a DE with 14M records will still take approximately 21-27 minutes to run, meaning with any major data increase these queries will time out.
  • Data flow is Synchonized DE -> Shared DE -> sub-BU DE per object.

Salesforce's own documentation on optimizing Queries is really basic and not of use this time around.

Here is as example Query

SELECT TOP 50 PERCENT
// attribute list //
FROM Synchronized DE
WHERE
// conditions // 
ORDER BY 
// condition //

1 Answer 1

11

My preferred method is to leverage the hidden, pseudo-identity _customObjectKey column to break up queries into manageable chunks. As long as your scheme can accommodate the overwrite-update/append-update/append-x pattern:

Here's a 4-group example using the mod operator:

  • Query 1: where de._customobjectKey % 4 = 0 /* action:overwrite */
  • Query 2: where de._customobjectKey % 4 = 1 /* action:append/update */
  • Query 3: where de._customobjectKey % 4 = 2 /* action:append/update */
  • Query 4: where de._customobjectKey % 4 = 3 /* action:append/update */

The first query would be an overwrite, then subsequent ones would be updates.

Also, if there's a date parameter that limits the scope of rows you should be considering, make a staging DE that includes only the most recently updated records, then base the subsequent queries on that staging DE.

3
  • Hey Adam, I'm trying to make sense of this. _CustomObjectKey is just a hidden unique identifier for a record in a DE, right? Can you help me understand the purpose of the MOD operator and how the _CustomObjectKey's value correlates with an overwrite/append/update?
    – Mike Marks
    Commented Jan 6, 2021 at 3:27
  • The modulo (%) operator returns the remainder of a division operation. The hidden _customObjectKey is just a semi-sequental number, so if you divide it by 4 (in this case) the remainder will always be between 0 and 3 for every potential value of _customObjectKey. If you employ this you have to first overwrite with the first query and then append or update with subsequent ones since all of the queries together should target a single DE. You don't want to do an for overwrite every group. Commented Jan 6, 2021 at 13:07
  • Ah yesss that makes sense. Thank you sir!!
    – Mike Marks
    Commented Jan 6, 2021 at 17:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .