All geek questions in one place

Super slow request ... What have I done wrong?

You guys are awesome. Over the past couple of days, I posted here twice - a new user, and I was shocked at the help. So, I decided that I would take the slowest request that I have in my software and see if anyone could help me speed it up. I use this query as a view, so it is important that it is fast (and it is not!).

Firstly, I have a contact table in which my company’s clients are stored. The table has a JobTitle column that contains the identifier that is defined in the Contacts_Def_JobFunctions table. There is also a table called contacts_link_job_functions, which contains the contact number and additional job functions that the client has - also defined in the Contacts_Def_JobFunctions table.

Secondly, the Contacts_Def_JobFunctions table entries have a parent / child relationship with themselves. Thus, we will group similar job functions (for example: maid, laundry, cleaning, cleaning, etc. - all the same main job - while the job title may change). Functions that we do not currently work with are supported as children of ParentJobID 1841.

Thirdly, institutions with zipcodesadditional simply provide geographic data for the final result.

Finally, like all responsible companies, we keep a list of deletions for any of our customers who want to unsubscribe from our newsletter (after selection).

I use the following query to create a table of those people who have decided to accept our newsletter and who have a job function or position related to the services / products that we offer.

Here is my UGLY request:

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

I hope some of you superstars help me tune this out.

Many thanks,

Russell Schutte

UPDATE - UPDATE - UPDATE - UPDATE - UPDATE

After receiving several feedback messages, especially from Hanzor, I worked a lot on setting up this request and came up with the following:

SELECT  DISTINCT
                  contacts_link_emails.Email, contacts.ContactID, contacts.First AS ContactFirstName, contacts.Last AS ContactLastName, contacts.InstitutionID, 
                  institutionswithzipcodesadditional.CountyID, institutionswithzipcodesadditional.StateID, institutionswithzipcodesadditional.DistrictID
FROM contacts 
INNER JOIN
    contacts_def_jobfunctions ON contacts.jobtitle = contacts_def_jobfunctions.JobID AND contacts_def_jobfunctions.ParentJobID <> '1841'
INNER JOIN
    contacts_link_jobfunctions ON contacts_link_jobfunctions.JobID = contacts_def_jobfunctions.JobID AND contacts_def_jobfunctions.ParentJobID <> '1841'
INNER JOIN
    contacts_link_emails ON contacts.ContactID = contacts_link_emails.ContactID 
INNER JOIN
    institutionswithzipcodesadditional ON contacts.InstitutionID =  institutionswithzipcodesadditional.InstitutionID
LEFT JOIN
    newsletterremovelist ON newsletterremovelist.emailaddress = contacts_link_emails.email
WHERE    
    newsletterremovelist.emailaddress IS NULL

( , - - , ). 40% , ( 100% , ).

, "". , SQL Studio. - ?

?

,

== == == == == == == == == == == == == == == ==

. :

SELECT DISTINCT 
                      contacts_link_emails.Email, contacts.contactID,  contacts.First AS ContactFirstName, contacts.Last AS ContactLastName, contacts.InstitutionID, 
                      institutionswithzipcodesadditional.CountyID, institutionswithzipcodesadditional.StateID, institutionswithzipcodesadditional.DistrictID
FROM         
    contacts INNER JOIN institutionswithzipcodesadditional
        ON contacts.InstitutionID = institutionswithzipcodesadditional.InstitutionID
    INNER JOIN contacts_link_emails 
        ON contacts.ContactID = contacts_link_emails.ContactID
    LEFT OUTER JOIN contacts_def_jobfunctions 
        ON contacts.JobTitle = contacts_def_jobfunctions.JobID AND contacts_def_jobfunctions.ParentJobID <> '1841'
    LEFT OUTER JOIN contacts_link_jobfunctions
        ON contacts_link_jobfunctions.JobID = contacts_def_jobfunctions.JobID AND contacts_def_jobfunctions.ParentJobID <> '1841' 
    LEFT OUTER JOIN
        newsletterremovelist ON newsletterremovelist.EmailAddress = contacts_link_emails.Email
WHERE     (newsletterremovelist.EmailAddress IS NULL)

, . , , , , , , zipcodesadditional contacts_link_emails, INNER JOINed ().

. INNER JOIN , , (< > 1841), - , , JobTitle AND JobFunctions. . JobTitle "", , JobFunction, , , INNER JOIN.

, LEFT OUTER JOINs, , , JobTitles, , , , - " " JobFunction, - . , " ".

, LEFT OUTER JOIN . , , ...

. , - , , .

,

, , , , , . . , :

( - ).

!

+3
3

WHERE . . , .

NOT IN , , , NULL.

, OR WHERE , , - , OR .

:

SELECT 
    *
FROM
    dbo.contacts AS c
INNER JOIN
    dbo.contacts_def_jobfunctions AS jf
    ON c.JobTitle = jf.JobId AND jf.ParentJobID <> '1841'
INNER JOIN
    dbo.contacts_link_emails AS e
    ON c.ContactID = e.ContactID AND jf.JobID = c.JobTitle 
LEFT JOIN
    dbo.newsletterremovelist AS rl
    ON e.Email = rl.EmailAddress
WHERE    
    rl.EmailAddress IS NULL

, , ( SELECT *), contacts_ref_jobfunctions_3, .

() ,

+6

, , , . , , .

0

. - , ?

Better yet, do it SHOWPLANand paste it into your question.

0
source

Source: https://habr.com/ru/post/1780777/

More articles:


All Articles