查询超时/使查询更高效(exacttarget)

Question

这是我的问题，有没有人能看到有什么方法可以让它更有效率，这样它就不会超时？我使用的是Exacttarget (Salesforce Marketing Cloud)。它有30分钟的超时限制。我试着到处移动东西，但似乎总是出错。我在某种程度上是SQL的初学者，但在过去的一周里，我一直在努力地学习它。我的问题如下。谢谢!

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

我同意卡尔的观点，您的主要性能影响在于引用_Open和_Click系统数据视图的子查询。但是，根据我使用ExactTarget (Salesforce Marketing Cloud)的经验，您只能运行'SELECT‘语句，不能以这种方式声明变量。

我建议在_Open和_Click数据视图上运行单独的查询，然后在查询中引用结果数据扩展。这可能需要更多的步骤。但是，您会发现整体处理时间更短。

对于第一个查询，我将为在过去3个月内打开或单击过的每个人创建一个数据扩展。然后在第二个查询中，我将使用" in“语句引用结果数据扩展。这将消除查询中的一个"OR“条件，这可能是代价高昂的。如果查询仍然表现不佳，我建议以一种避免"OR“条件的方式重写RegistrationData数据扩展上的条件逻辑。

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'
    )
)

Answer 2

我要试一试。可能有一些次要的东西，但在我看来，唯一应该让查询旋转很长时间的是

            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
            )

这里有两个问题。首先，您正在执行日期数学无数次，然后使用IN (SELECT ...)这几乎可以肯定是低效的。

要解决第一个问题，请计算一个测试日期并使用它。对于第二个首选，使用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)   

您还可以展开EXISTS并使用连接到_Open和_Click，但这感觉更复杂。

试一试，让我们知道它是否有帮助。