Super Slow Query... What Have I Done Wrong?

You guys are amazing. I've posted here twice in the past couple of days - a new user - and I've been blown away by the help. So, I figured I'd take the slowest query I've got in

Solution 1:

Move the queries in your WHERE out to actual joins. These are called correlated subqueries, and are the work of the Voldemort. If they are joins, they are only executed once, and will speed up your query.

For the NOT IN sections, use a left outer join, and check that the column you joined on is NULL.

Also, avoid using OR in WHERE queries where possible - remember that OR is not neccesarily a short circuit operation.

An example is as follows:

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

Please don't use this, as it's almost certainly incorrect (not to mention SELECT *), I've ignored the logic for contacts_ref_jobfunctions_3 to provide a simple example.

For a (really) nice explanation of joins, try this visual explanation of joins

Solution 2:

Create some views representing some common associations that you make so that your sub-query is simpler. Also views execute a bit quicker as they do not need to be interpreted each time they are run.

Solution 3:

It could be any number of things. My first question is are the columns you're joining on indexed?

Better yet, do a SHOWPLAN and paste it into your question.