I am running a simple select * with three UNIONS across 3 data extensions with only 8 fields each and it takes forever to run, like 5 minutes. Should I make three separate queries instead and run them concurrently? What gives with the UNION slowness?

DEs: 8 fields Number Text/128 Number Text/8 Text/50 EmailAddress EmailAddress Date

Column count: <100 in any one DE, <200 across all 3

What it's essentially doing is pulling the recipient lists of three different emails and adding them to a single daily log. This log gets referenced to determine which email should be sent.

  • 2
    Is this question about marketing cloud? – Adrian Larson Jan 31 '17 at 16:27
  • Yes - sorry about that! – Matthew Wash Jan 31 '17 at 16:30
  • Just wanted to make sure you were on the proper forum. :) – Adrian Larson Jan 31 '17 at 16:31
  • How large are your DEs? And what data types are your keys? Maybe you could update your question with your query? – Adam Spriggs Jan 31 '17 at 17:08
  • Small row count. We're talking <100 in each DE and <200 total. Good idea I'll update the question – Matthew Wash Jan 31 '17 at 17:14

Unions are computationally expensive. This is because in SQL server , a simple union does 2 queries and concatenates them. In addition to this, it performs a "sort" operation, so that there are no duplicate records (i.e if a contact exists in both A & B, return A).

A "Union All" will speed things up significantly as it will not perform the "distinct" operation and WILL return duplicates.

| improve this answer | |
  • Agree somewhat, if we were talking about larger datasets. SQL Server will process either a UNION ALL or a plain UNION on this data in milliseconds. The difference in speed would be imperceptible. The main "issue" isn't around SQL tuning, but rather the time it takes the slot servers to pick up the job and execute that 5ms query. The queue to the slot servers isn't in our control and it's just what it is. – Macca Feb 1 '17 at 10:03
  • The queue been out of our control is a problem that is not just unique to UNION. As this question refers to UNION and not general T-SQL functionality on SFMC, I think addressing query optimisation is the way forward. Adding staging tables would definitely boost performance. – Samuel Dare Feb 1 '17 at 13:03
  • 1
    Adding staging tables for a tiny dataset, like this, would slow down your automation with multiple sub-second queries each taking five minutes. The question was "why are unions taking so long to execute?" The answer is they aren't taking any perceivable time longer than other queries in this instance. Grab some tables with a few million records in each and then the answer is different. – Macca Feb 1 '17 at 21:27

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.