Супер медленный запрос - ускорился, но не идеально... Пожалуйста, помогите

Question

Я вчера отправил запрос (см. Здесь), который был ужасен (заняло более минуты, чтобы получить 18 215 записей):

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

С большим количеством обучения и исследований я настроил это на следующее:

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 строк, которые я считаю точными).

Результаты кажутся почти правильными. Я работаю, чтобы выяснить, какие данные могут отсутствовать в наборе результатов.

Не могли бы вы научить меня тому, что я здесь сделал неправильно?

Спасибо,

Рассел Шутте

Answer 1

Основная проблема с вашим исходным запросом заключалась в том, что у вас было два дополнительных объединения только для введения дубликатов, а затем DISTINCT чтобы избавиться от них.

Использовать этот:

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)

Answer 2

Я не совсем уверен, что не так, но когда я сталкиваюсь с этой ситуацией, первым делом я начинаю удалять переменные.

Итак, закомментируйте предложение where. Сколько строк возвращается?

Если вы вернете 11 604 строки, то вы изолировали проблемы для объединений. Работайте через объединения, комментируя каждое из них (тоже удалите связанные столбцы) и выясните, сколько строк исключено.

Делая это, стремитесь найти то, что вызывает удаление нужных строк. После изоляции рассмотрите разницу между первым и вторым запросами.

---

Рассматривая первый запрос, вы, вероятно, можете просто изменить его, чтобы устранить любые INи вместо этого сделать EXISTS вместо.

Рассмотрите ваши индексы также. Любая вещь в предложениях where или join должна быть проиндексирована.

Answer 3

Получаете ли вы такие же результаты, когда делаете:

SELECT count(*)
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  
SELECT COUNT(*)        
FROM        
    contacts 
INNER JOIN contacts_link_jobfunctions 
    ON contacts.ContactID = contacts_link_jobfunctions.ContactID 
INNER JOIN  contacts_link_emails 
    ON contacts.ContactID = contacts_link_emails.ContactID 

Если это так, продолжайте добавлять каждое условие присоединения, пока не получите те же результаты, и вы увидите, где была ваша ошибка. Если все объединения одинаковы, посмотрите на предложения where. Но я буду удивлен, если это не будет в первом соединении, потому что синтаксис, который вы изначально использовали, даже не будет работать на SQL Server, и это довольно нестандартный SQL и, возможно, был неправильным все время, но никто не знал.

В качестве альтернативы, выберите несколько записей, которые возвращены в оригинальной, но не пересмотренной. Отслеживайте их по таблицам по одной за раз, чтобы узнать, сможете ли вы найти причину, по которой второй запрос их отфильтровывает.