Databases
StackOverflow2013
Postsdbo
POSuper Slow Query - sped up, but not perfect... Please help
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSuper Slow Query - sped up, but not perfect... Please help
    text
    copied!<p>I posted a query yesterday (see <a href="https://stackoverflow.com/questions/4466054/super-slow-query-what-have-i-done-wrong">here</a>) that was horrible (took over a minute to run, resulting in 18,215 records):</p> <pre><code>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 &lt;&gt; '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 &lt;&gt; '1841'))) AND (dbo.contacts_link_emails.Email NOT IN (SELECT EmailAddress FROM dbo.newsletterremovelist AS newsletterremovelist)) ORDER BY EMAIL </code></pre> <p>With a lot of coaching and research, I've tuned it up to the following:</p> <pre><code>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 &lt;&gt; 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 &lt;&gt; 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)) </code></pre> <p>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).</p> <p>The results seem near correct. I'm working to discover what data might be missing in the result set.</p> <p>Can you please coach me through whatever I've done wrong here?</p> <p>Thanks,</p> <p>Russell Schutte</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload