Exclude records matching subquery

Question

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

Answer 1

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

Answer 2

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)

Answer 3

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.

Answer 4

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