Query timing out / making query more efficient (exacttarget)

Question

Here is my query, can anyone see any way to make this more efficient so it doesn't time out? I'm using Exacttarget (Salesforce Marketing Cloud). It has a 30 minute timeout limit. I've tried moving things around but it always seems to error. I'm kind of a beginner with SQL but I've been hitting it fairly hard the last week. My query is below. THanks!

SELECT DISTINCT c.Email,  c.FName
FROM ENT.Contacts c WITH(NOLOCK)
INNER JOIN ENT.RegistrationData r WITH(NOLOCK)
ON c.Email  =  r.RegistrationContactEmail
LEFT Join ENT._Subscribers s  WITH(NOLOCK)
ON c.Email =  s.SubscriberKey
AND s.status NOT IN ('unsubscribed','held')

WHERE

(
    (
        (
            (
                r.RegistrationEmailOptStatus  =  '1' AND
                r.RegistrationEventType  =  'Wedding' AND
                r.RegistrationEventRole IN ('Bride','Other','Bridesmaid','Mother Of the Bride') AND
                r.RegistrationCountry IN ('USA') AND
                r.RegistrationEventDate < '2014-05-31'
            )

            OR

            (
                r.RegistrationEmailOptStatus  =  '1' AND
                r.RegistrationEventType  =  'Prom' AND
                r.RegistrationEventRole ='Prom' AND
                r.RegistrationCountry IN ('USA') AND
                r.RegistrationEventDate BETWEEN '2014-01-01' AND '2015-12-31'
            )
        )

        AND

        (
            (
                c.Email IN
                (
                    SELECT DISTINCT 
                        s.SubscriberKey AS Email
                    FROM 
                        _Open s
                    WHERE
                        datediff(mm,s.EventDate, getdate()) <= 3
                )
            )
            OR
            (
                c.Email IN
                (
                    SELECT DISTINCT 
                        s.SubscriberKey AS Email
                    FROM 
                        _Click s
                    WHERE
                        datediff(mm,s.EventDate, getdate()) <= 3
                )
            )
        )

    )

    OR

    (
        r.RegistrationEmailOptStatus  =  '1' AND
        r.RegistrationEventType  =  'Wedding' AND
        r.RegistrationEventRole IN ('Bride','Other','Bridesmaid','Mother Of the Bride') AND
        r.RegistrationCountry IN ('USA') AND
        r.RegistrationEventDate BETWEEN '2015-05-01' AND '2015-05-31'
    )
)

Answer 1

I'll take a shot. There maybe some minor stuff, but the only thing that looks to me like it should make the query spin a long time is

            c.Email IN
            (
                SELECT DISTINCT 
                    s.SubscriberKey AS Email
                FROM 
                    _Open s
                WHERE
                    datediff(mm,s.EventDate, getdate()) <= 3
            )
        OR
            c.Email IN
            (
                SELECT DISTINCT 
                    s.SubscriberKey AS Email
                FROM 
                    _Click s
                WHERE
                    datediff(mm,s.EventDate, getdate()) <= 3
            )

There are two issues in there. First you are doing date math a bajillion times and second using IN (SELECT ...) here is almost certainly inefficient.

To address the first, calculate a single test date to and use that. For the second prefer checking with EXISTS.

DECLARE @testDate DATE = DATEADD(mm,3,GETDATE())

...

        EXISTS(SELECT 1 FROM _Open s WHERE s.EventDate>@testDate AND c.Email = s.SubscriberKey)
     OR EXISTS(SELECT 1 FROM _Click s WHERE s.EventDate>@testDate AND c.Email = s.SubscriberKey)   

You could also probably unwind the EXISTS and use joins to _Open and _Click, but that feels more complex.

Give this a shot and let us know if it helps.

Answer 2

I agree with Karl that your main performance hit is in the sub-query that references the _Open and _Click system data views. But, based on my experience with the ExactTarget (Salesforce Marketing Cloud), you are limited to only running 'SELECT' statements and will not be able to declare a variable this way.

I recommend running a separate query on the _Open and _Click data view and then reference the resulting data extension in your query. This may require more steps. But, you'll find the overall processing time is less.

For the first query, I would create a data extension of everyone that has either opened or clicked in the last 3 months. And then in the second query, I would reference the resulting data extension with a "IN" statement. This will eliminate one of the "OR" conditions in your query, which can be expensive. If the query still preforms poorly, I would suggest re-writing the conditional logic on the RegistrationData data extension in a way to a avoids "OR" conditions.

Query1:

SELECT DISTINCT s.SubscriberKey AS Email
FROM  _Open s WITH(NOLOCK)
WHERE datediff(mm,s.EventDate, getdate()) <= 3

union all

SELECT DISTINCT s.SubscriberKey AS Email
FROM _Click s WITH(NOLOCK)
WHERE datediff(mm,s.EventDate, getdate()) <= 3

Query2:

SELECT DISTINCT c.Email,  c.FName
FROM ENT.Contacts c WITH(NOLOCK)
INNER JOIN ENT.RegistrationData r WITH(NOLOCK)
ON c.Email  =  r.RegistrationContactEmail
LEFT Join ENT._Subscribers s  WITH(NOLOCK)
ON c.Email =  s.SubscriberKey
AND s.status NOT IN ('unsubscribed','held')
WHERE  
(
    (
        (
            (
                r.RegistrationEmailOptStatus  =  '1' AND
                r.RegistrationEventType  =  'Wedding' AND
                r.RegistrationEventRole IN ('Bride','Other','Bridesmaid','Mother Of the Bride') AND
                r.RegistrationCountry IN ('USA') AND
                r.RegistrationEventDate < '2014-05-31'
            )

            OR

            (
                r.RegistrationEmailOptStatus  =  '1' AND
                r.RegistrationEventType  =  'Prom' AND
                r.RegistrationEventRole ='Prom' AND
                r.RegistrationCountry IN ('USA') AND
                r.RegistrationEventDate BETWEEN '2014-01-01' AND '2015-12-31'
            )
        )

        AND

        (
            c.Email  in (
                select s.SubscriberKey
                from OpenOrClickDE s
                where s.SubscriberKey = c.Email            
            )
        )

    )

    OR

    (
        r.RegistrationEmailOptStatus  =  '1' AND
        r.RegistrationEventType  =  'Wedding' AND
        r.RegistrationEventRole IN ('Bride','Other','Bridesmaid','Mother Of the Bride') AND
        r.RegistrationCountry IN ('USA') AND
        r.RegistrationEventDate BETWEEN '2015-05-01' AND '2015-05-31'
    )
)