查询性能太慢

Question

我的查询性能问题。如果我删除状态列，它运行速度非常快，但在列部分中添加子查询会延迟太多查询1.02分钟。我怎样才能修改这个查询，以便快速获得所需的数据。

我把这个子查询放在那里的原因是因为我需要最新活动的状态，有些活动的状态为空，所以我不得不忽略它们。

场所：6.5K行 - EstablishmentActivities：70K行 - 状态：2（有效，无效）

SELECT DISTINCT 
    est.id, 
    est.trackingNumber, 
    est.NAME AS 'establishment', 
    actTypes.NAME AS 'activity', 
    (
     SELECT stat3.NAME 
     FROM SACPAN_EstablishmentActivities eact3 
     INNER JOIN SACPAN_ActivityTypes at3 
      ON eact3.activityType_FK = at3.code 
     INNER JOIN SACPAN_Status stat3 
      ON stat3.id = at3.status_FK 
     WHERE eact3.establishment_FK = est.id 
      AND eact3.rowCreatedDT = (
      SELECT MAX(est4.rowCreatedDT) 
      FROM SACPAN_EstablishmentActivities est4 
      INNER JOIN SACPAN_ActivityTypes at4 
       ON est4.establishment_fk = est.id 
       AND est4.activityType_FK = at4.code 
      WHERE est4.establishment_fk = est.id 
       AND at4.status_FK IS NOT NULL 
     ) 
      AND at3.status_FK IS NOT NULL 
    ) AS 'status', 
    est.authorizationNumber, 
    reg.NAME AS 'region', 
    mun.NAME AS 'municipality', 
    ISNULL(usr.NAME, '') + ISNULL(+ ' ' + usr.lastName, '') 
     AS 'created', 
    ISNULL(usr2.NAME, '') + ISNULL(+ ' ' + usr2.lastName, '') 
     AS 'updated', 
    est.rowCreatedDT, 
    est.rowUpdatedDT, 
    CASE WHEN est.rowUpdatedDT >= est.rowCreatedDT 
     THEN est.rowUpdatedDT 
     ELSE est.rowCreatedDT 
    END AS 'LatestCreatedOrModified' 
FROM SACPAN_Establishments est 
INNER JOIN SACPAN_EstablishmentActivities eact 
    ON est.id = eact.establishment_FK 
INNER JOIN SACPAN_ActivityTypes actTypes 
    ON eact.activityType_FK = actTypes.code 
INNER JOIN SACPAN_Regions reg 
    ON est.region_FK = reg.code -- 
INNER JOIN SACPAN_Municipalities mun 
    ON est.municipality_FK = mun.code 
INNER JOIN SACPAN_ContactEstablishments ce 
    ON ce.establishment_FK = est.id 
INNER JOIN SACPAN_Contacts con 
    ON ce.contact_FK = con.id 
--JOIN SACPAN_Status stat ON stat.id = actTypes.status_FK 
INNER JOIN SACPAN_Users usr 
    ON usr.id = est.rowCreatedBy_FK 
LEFT JOIN SACPAN_Users usr2 
    ON usr2.id = est.rowUpdatedBy_FK 
WHERE (con.ssn = @ssn OR @ssn = '*') 
    AND eact.rowCreatedDT = (
    SELECT MAX(eact2.rowCreatedDT) 
    FROM SACPAN_EstablishmentActivities eact2 
    WHERE eact2.establishment_FK = est.id 
) 
--AND est.id = 6266 
ORDER BY 'LatestCreatedOrModified' DESC 

Answer 1

我解决了这个问题，通过创建一个临时表并为其创建一个索引，这样就不再需要select语句中的慢子查询。然后我像加普通表一样加入临时表。

感谢所有。

Answer 2

尝试移动那activiy“查询为左连接，看看如果它加快速度。