超级慢查询-加快了速度，但并不完美...请帮助 [英] Super Slow Query - sped up, but not perfect... Please help

问题描述

我昨天发布了一个查询(请参见此处)那太可怕了(花了一分钟时间，导致18,215条记录):

I posted a query yesterday (see here) that was horrible (took over a minute to run, resulting in 18,215 records):

SELECT DISTINCT 
    dbo.contacts_link_emails.Email, dbo.contacts.ContactID, dbo.contacts.First AS ContactFirstName, dbo.contacts.Last AS ContactLastName, dbo.contacts.InstitutionID, 
    dbo.institutionswithzipcodesadditional.CountyID, dbo.institutionswithzipcodesadditional.StateID,  dbo.institutionswithzipcodesadditional.DistrictID
FROM         
    dbo.contacts_def_jobfunctions AS contacts_def_jobfunctions_3 
INNER JOIN
    dbo.contacts 
INNER JOIN
    dbo.contacts_link_emails 
        ON dbo.contacts.ContactID = dbo.contacts_link_emails.ContactID 
        ON contacts_def_jobfunctions_3.JobID = dbo.contacts.JobTitle 
INNER JOIN
    dbo.institutionswithzipcodesadditional 
        ON dbo.contacts.InstitutionID = dbo.institutionswithzipcodesadditional.InstitutionID 
LEFT OUTER JOIN
    dbo.contacts_def_jobfunctions 
INNER JOIN
    dbo.contacts_link_jobfunctions 
        ON dbo.contacts_def_jobfunctions.JobID = dbo.contacts_link_jobfunctions.JobID 
        ON dbo.contacts.ContactID = dbo.contacts_link_jobfunctions.ContactID
WHERE     
        (dbo.contacts.JobTitle IN
        (SELECT     JobID
        FROM          dbo.contacts_def_jobfunctions AS contacts_def_jobfunctions_1
        WHERE      (ParentJobID <> '1841'))) 
    AND
        (dbo.contacts_link_emails.Email NOT IN
        (SELECT     EmailAddress
        FROM          dbo.newsletterremovelist)) 
OR
        (dbo.contacts_link_jobfunctions.JobID IN
        (SELECT     JobID
        FROM          dbo.contacts_def_jobfunctions AS contacts_def_jobfunctions_2
        WHERE      (ParentJobID <> '1841')))
    AND 
        (dbo.contacts_link_emails.Email NOT IN
        (SELECT     EmailAddress
        FROM          dbo.newsletterremovelist AS newsletterremovelist)) 
ORDER BY EMAIL

经过大量的指导和研究，我将其调整为以下内容:

With a lot of coaching and research, I've tuned it up to the following:

SELECT  contacts.ContactID,
        contacts.InstitutionID,
        contacts.First,
        contacts.Last,
        institutionswithzipcodesadditional.CountyID, 
        institutionswithzipcodesadditional.StateID,
        institutionswithzipcodesadditional.DistrictID
FROM    contacts 
    INNER JOIN contacts_link_emails ON 
    contacts.ContactID = contacts_link_emails.ContactID
    INNER JOIN institutionswithzipcodesadditional ON
    contacts.InstitutionID = institutionswithzipcodesadditional.InstitutionID
WHERE
    (contacts.ContactID IN
        (SELECT contacts_2.ContactID
        FROM contacts AS contacts_2
        INNER JOIN contacts_link_emails AS contacts_link_emails_2 ON
            contacts_2.ContactID = contacts_link_emails_2.ContactID
        LEFT OUTER JOIN contacts_def_jobfunctions ON 
            contacts_2.JobTitle = contacts_def_jobfunctions.JobID
        RIGHT OUTER JOIN newsletterremovelist ON 
            contacts_link_emails_2.Email = newsletterremovelist.EmailAddress
        WHERE (contacts_def_jobfunctions.ParentJobID <> 1841)
        GROUP BY contacts_2.ContactID
    UNION
        SELECT contacts_1.ContactID
        FROM contacts_link_jobfunctions
        INNER JOIN contacts_def_jobfunctions AS contacts_def_jobfunctions_1 ON
            contacts_link_jobfunctions.JobID = contacts_def_jobfunctions_1.JobID 
            AND contacts_def_jobfunctions_1.ParentJobID <> 1841 
        INNER JOIN contacts AS contacts_1 ON 
            contacts_link_jobfunctions.ContactID = contacts_1.ContactID
        INNER JOIN contacts_link_emails AS contacts_link_emails_1 ON
            contacts_link_emails_1.ContactID = contacts_1.ContactID
        LEFT OUTER JOIN newsletterremovelist AS newsletterremovelist_1 ON
        contacts_link_emails_1.Email = newsletterremovelist_1.EmailAddress
        GROUP BY contacts_1.ContactID))

虽然此查询现在非常快(大约3秒)，但我已经将逻辑的一部分吹散了-它仅返回14,863行(而不是我认为是准确的18,215行).

While this query is now super fast (about 3 seconds), I've blown part of the logic somewhere - it only returns 14,863 rows (instead of the 18,215 rows that I believe is accurate).

结果似乎接近正确.我正在努力寻找结果集中可能缺少的数据.

The results seem near correct. I'm working to discover what data might be missing in the result set.

您能通过我在这里做错的事情来指导我吗?

Can you please coach me through whatever I've done wrong here?

谢谢

罗素·舒特(Russell Schutte)

Russell Schutte

推荐答案

原始查询的主要问题是，您有两个额外的联接只是为了引入重复项，然后有一个DISTINCT来消除它们.

The main problem with your original query was that you had two extra joins just to introduce duplicates and then a DISTINCT to get rid of them.

使用此:

SELECT  cle.Email,
        c.ContactID,
        c.First AS ContactFirstName,
        c.Last AS ContactLastName,
        c.InstitutionID, 
        izip.CountyID,
        izip.StateID, 
        izip.DistrictID
FROM    dbo.contacts c
INNER JOIN
        dbo.institutionswithzipcodesadditional izip
ON      izip.InstitutionID = c.InstitutionID
INNER JOIN
        dbo.contacts_link_emails cle
ON      cle.ContactID = c.ContactID 
WHERE   cle.Email NOT IN
        (
        SELECT  EmailAddress
        FROM    dbo.newsletterremovelist
        )
        AND EXISTS
        (
        SELECT  NULL
        FROM    dbo.contacts_def_jobfunctions cdj
        WHERE   cdj.JobId = c.JobTitle
                AND cdj.ParentJobId <> '1841'
        UNION ALL
        SELECT  NULL
        FROM    dbo.contacts_link_jobfunctions clj
        JOIN    dbo.contacts_def_jobfunctions cdj
        ON      cdj.JobID = clj.JobID
        WHERE   clj.ContactID = c.ContactID
                AND cdj.ParentJobId <> '1841'
        )
ORDER BY
        email

创建以下索引:

newsletterremovelist (EmailAddress)
contacts_link_jobfunctions (ContactID, JobID)
contacts_def_jobfunctions (JobID)

这篇关于超级慢查询-加快了速度，但并不完美...请帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！