Working with Query Activities in Salesforce Marketing Cloud

Working with Query Activities

Eliot Harper

10 July, 2019

data

Overview

In this episode I share tips and techniques for creating SQL query activities in Salesforce Marketing Cloud.

Transcript

Hello and welcome to mcChat — the video channel that puts the fun into Salesforce Marketing Cloud. My name is Eliot Harper and I’ve put this video together to share tips and best practices to help you get the most out of Salesforce Marketing Cloud.

Now in this very first episode, I’ll explain the platform behaviour for couple of different scenarios when writing SQL query activities in Automation Studio.

I know that many users are unaware that the platform doesn’t literally interpret your queries, but applies additional processes to optimise your queries when you save them.

Tip 1: Select all Data Extension Fields

You’ve probably used the star wildcard character in SQL statements to include all fields from a Data Extension. This can be a great time-saver, particularly when working with Data Extensions with dozens of fields as it provides a convenient method to include all fields in a Data Extension, rather than having to type each field in turn.

However, when you save your SQL query, the platform retrieves all the fields in the Data Extension and uses these field names instead of the wildcard character. So, if your Data Extension schema changes, for example, you add new fields to the Data Extension used in the FROM clause, these fields won’t be included in your query until you re-save the query activity.

Also, as another tip, you can conveniently add all fields in a Data Extension to your query simply by navigating to the Data Extension in the left panel and dragging the Data Extension to your query, which automatically adds all fields from the Data Extension to your query. That’s probably a safer way to include all fields, as you can easily see which fields are included.

Tip 2: NOLOCK Hint

The NOLOCK hint in SQL prevents reads being deadlocked by other operations; in other words it allows the query to read data from Data Extensions by ignoring any locks imposed on them, and therefore prevents them from blocked by other processes.

And I’ve seen many users insist on including the WITH NOLOCK hint in their queries, with the belief that their query activities will actually run faster.

But they don’t. You see, the platform already applies this hint to your queries under-the-hood, it has absolutely no impact as the system wraps this hint for you — the NOLOCK hint is always enforced, no matter what your query looks like, so there’s no need to include it.

Well, that's all we have time for. Don’t forget to subscribe to this video channel to for other tips in working with Salesforce Marketing Cloud!

@@vuIhao3Cpcc@@
Previous Video
Next Video

Schedule a Call

If you would like to discuss how you can get the most out of Salesforce Marketing Cloud, then Eliot would love to chat with you! Please click on the button to schedule a call.

Schedule a call with Eliot