by 
From emails to chains of SQL queries, having a multi-step list is a great best practice to follow when things are breaking, but should be working. Building a beautiful, but complex code, where multiple requirements are accomplished in a few lines of code may seem like an efficient approach, but sometimes the best solution is to break it down.
We see two major advantages to taking this approach:
- It becomes easier to understand and verify each part is working how it’s expected to.
- It becomes easier on the system (especially within SFMC Automation Studio).
I have personally seen many instances where breaking one large query that continuously times out or fails into two to three smaller queries fixes the issues. There have also been instances where 70+ line queries that are well written and logical fail simply because they took too long, so breaking them down into a few individual queries allows them to run in five minutes or less.
The Strategy Breakdown:
- Whether it is email scripting or SQL queries, break out what the complex code is doing.
- Divide them into their own process, query, or section.
- Recombine the result sets AFTER they have been completely processed on their own.
A SQL query that has multiple sub queries and filters off of the results with complex ‘where’ or ‘case’ statements would be ripe to break down into its component parts. So instead of one massive query, run the two to three subqueries as their own steps, get their results loaded into tables, and then have the larger query run at the end. The difference this time is having it pull the pre-filtered/processed data from the tables populated by the subqueries.
Example:
The below SQL is a good example of subqueries that could be broken out into their own steps. The below SQL is using activity archive tables that record sends, opens, and clicks to output a list of records that interacted with a specific email, as designated by the where SendID = ‘26959437’ portion of the code.
If this query was timing out, a great starting point would be to triage the innermost subquery. From there, it would be turned into its own step with its own DE where the results would be written. And then, as a final step, modify the existing query to reference the new result set.
See the pictures below for more details.
Complete Query:
New step 1 query: The results would then be loaded into a new data extension, let’s call it ‘Job_Archive_Holding’.
New step 2 query: The subquery from step 1 was replaced and is now doing a direct query on the results saved in the data extension populated by step 1.
Figuring out how to break complex code into its component parts can be useful, especially if a portion of the code is breaking or not behaving as expected. Breaking code down and running it step by step or section by section can be beneficial.
Optional: Break it yourself and try to rebuild it.
If you have reached this point of the checklist and the code still isn’t working, now is the time to smash it to pieces. At this point in the process, taking time to fully outline what the final output or goal needs to be, is crucial. This is where teamwork and a second (or third) set of eyes becomes invaluable – have another expert take a look with you.
The great thing about programming, in general, is that there are almost always multiple ways to accomplish a goal. Here are some steps to get there:
- Rewrite your code and simplify each step to its simplest and purest form.
- Accomplish one goal at a time, make sure that goal is cemented/working, and then move on.
- Always keep in mind what your end goal is too.
- Also, while you can take elements from your previous code/solutions, do try and stretch your brain to consider other ways to accomplish each goal. Especially if it wasn’t working how you tried before.
- Come at the problem from another perspective.
These strategies have worked great in data investigations and a wide variety of technical projects. They are a handy set of tools and guidelines!
Thank you for reading, you’ve been a fantastic audience!
