Exclude records matching subquery

There is probably an obvious answer to this question, but I am having one heck of a time getting anywhere with it.

Consider the query:

SELECT *
FROM reports AS r
JOIN reportvalues AS rv ON rv.report_id = r.report_id
JOIN metrics AS m ON m.metric_id = rv.metric_id
WHERE r.report_id NOT IN(
    SELECT DISTINCT report_id
    FROM exclude_report
)

In this query, exclude_report is a view constructed in a similar manner.

Now what happens is the query开发者_Python百科 takes an EXTREMELY long time to execute, presumably because the subquery is being executed over each and every row in the parent query. However, I am finding no feasible means of doing this any other way.

Oh great SQL wizards, please advise. I really need a way of doing this all in SQL, which I will be using in SSRS.

---

The distinct is probably killing you, you don't need distinct in a subquery when using in

Is this better?

SELECT *
FROM reports AS r
JOIN reportvalues AS rv ON rv.report_id = r.report_id
JOIN metrics AS m ON m.metric_id = rv.metric_id
WHERE NOT EXISTS (SELECT 1 
        FROM exclude_report e 
        WHERE e.report_id = r.report_id)

---

SELECT *
FROM reports AS r
JOIN reportvalues AS rv ON rv.report_id = r.report_id
JOIN metrics AS m ON m.metric_id = rv.metric_id
LEFT JOIN exclude_report er ON r.report_id = er.report_id
WHERE er.report_id IS NULL

---

You don't need the distinct keyword, which can be heavy. I'd also advise you to add indices on the foreign keys of large tables.

---

I think you shold have the followings indexes:

• field report_id on table report
• field report_id on table reportvalues
• field metric_id on table reportvalues
• field metric_id on table metrics
• field report_id on table exclude_report