0

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 //
8

My go-to 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.

Those are a couple of tactics off the top of my head.

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
    Jan 6 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. Jan 6 at 13:07
  • Ah yesss that makes sense. Thank you sir!!
    – Mike Marks
    Jan 6 at 17:34

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.