SQL查询执行时间很快，但获取行很慢

Question

这是一个由两部分组成的问题，但首先是一些背景信息：

我在 Sybase 中有一个 TSQL 查询，报告执行时间为 0.328 秒，但检索大约 5000 行需要大约 20-30 秒。该查询有两个子查询和一个左外连接。

该查询大致如下：

SELECT CustomerContact.Id, Customer.Name, ... 
     , CustomerContacts.LastName, CustomerContacts.FirstName
     , ( SELECT max(LastModified)
             FROM ContactPhone
             WHERE ContactPhone.ContactID = CustomerContact.ID
       ) as PhoneLastModified
     , ( SELECT max(LastModified)
             FROM ContactEmail
             WHERE ContactEmail.ContactID = CustomerContact.ID
       ) as EmailLastModified
    FROM CustomerContacts
    LEFT OUTER JOIN Customer
        ON Customer.ID = CustomerContact.CustomerId
    WHERE (PhoneLastModified > '2011-01-01'
        OR EmailLastModified > '2011-01-01')

我正在做的是根据任何关联联系信息的上次修改日期选择客户记录。对于 CustomerContact 中的任何给定行，ContactPhone 和 ContactEmail 可以包含 x 条记录。 Customer 表与 CustomerContact 是一对一的。

现在我的问题是：

1. 为什么 Sybase 报告执行时间为 0.328 秒，但实际上检索查询中的行需要接近 30 秒？

2. 我可以做什么来优化这个查询？

我的第一个想法是向 LastModified 列添加索引，但我正在处理少量记录。

我的第二个想法是子查询正在减慢速度，我应该将它们转换为联接。但我无法在连接条件中使用聚合函数 max，那么如何仅获取连接中的最大行呢？

谢谢

Answer 1

我猜测 select 子句中的 2 个相关子查询在返回行之前不会执行。一般来说，应该避免相关子查询，因为它们往往很慢，当然总有例外！

尝试将 ContactPhone 和 Contact Email 移动到连接的子查询中。

SELECT 
    cc.Id, 
    c.Name,
    ... , 
    cc.LastName, CustomerContacts.FirstName,
    cp.LastModified PhoneLastModified
    ce.LastModified EmailLastModified
FROM 
    CustomerContacts cc
LEFT OUTER JOIN 
    Customer c 
ON 
    c.ID = cc.CustomerId
INNER JOIN 
    (SELECT
        ContactId,
        max(LastModified) as LastModified
     FROM
        ContactPhone
     WHERE
         LastModified > '2011-01-01'
     GROUP BY
     ContactId ) cp
ON 
    cp.ContactID = cc.ID
INNER JOIN 
    (SELECT
        ContactId,
        max(LastModified) as LastModified
     FROM
        ContactEmail
     WHERE
         LastModified> '2011-01-01'
     GROUP BY
     ContactId ) ce
ON 
    ce.ContactID = cc.ID

Answer 2

我现在看到他使用的是 SYBASE 而不是 SQL Server（TSQL 也可以是），但我会将答案留给其他使用 MS 产品的人。

这是 CTE 版本。与 Paul 的版本相同，但更容易阅读：

WITH MaxContactPhone AS
(
   SELECT max(LastModified) as LastModified, ContactID 
   FROM ContactPhone
   WHERE LastModified> '2011-01-01'
   GROUP BY ContactID
), MaxContactEmail AS
(
   SELECT max(LastModified) as LastModifed, ContactID
   FROM ContactEmail
   WHERE LastModified> '2011-01-01'
   GROUP BY ContactID
)
SELECT CustomerContact.Id, Customer.Name, ... , CustomerContacts.LastName, 
       CustomerContacts.FirstName,
       MaxContactPhone.LastModified as PhoneLastModified,
       MaxContactEmail.LastModified as EmailLastModified
    FROM CustomerContacts
    LEFT OUTER JOIN Customer ON Customer.ID = CustomerContact.CustomerId
    JOIN MaxContactPhone ON CustomerContact.CustomerId = MaxContactPhone.ContactID AND 
    JOIN MaxContactEmail ON CustomerContact.CustomerId = MaxContactEmail.ContactID

Answer 3

SELECT cc.ID, cu.Name, ... 
     , cc.LastName, cc.FirstName
     , g.PhoneLastModified
     , g.EmailLastModified
    FROM CustomerContacts cc
    LEFT JOIN Customer cu
        ON cu.ID = cc.CustomerID
    JOIN 
      ( SELECT cc.ID
             , max(cp.LastModified)
               AS PhoneLastModified
             , max(ce.LastModified)
               AS EmailLastModified
            FROM CustomerContacts cc
            LEFT JOIN ContactPhone cp
                ON cp.ContactID = cc.ID
            LEFT JOIN ContactEmail ce
                ON ce.ContactID = cc.ID
            GROUP BY cc.ID
            HAVING ( PhoneLastModified > '2011-01-01'
                  OR EmailLastModified > '2011-01-01' )
      ) AS g
        ON g.Id = cc.id