Select where not in another table

Question

I am trying to select all submissions which don't exist in Quote or Policy table.

select
COUNT (*)
from Submission S
where
(S.Submission_Status='New' OR S.Submission_Status='Quoted to Agent' OR S.Submission_Status='Cleared')
and YEAR(S.Submission_Date) >= YEAR( GETDATE())
and S.Submission not in (select distinct Submission from Policy where Submission is not null) 
and S.Submission not in (select distinct Submission from Quote where Submission is not null)

This query returns good results but query is extremely slow (since there are lot of records in Policy and Quote tables). Is it possible to make faster version of this query (I guess with JOIN)?

Thanks,
Ilija

EDIT:
I have tried this but results don't match first query:

Answer 1

I'd try replacing not in with not exist, this way you won't distinct. Ultimate solution - if you are sure that performance degrades because of large number of records in policy and quote, create indexed view on each of the table (select distinct …). In some sqlserver editions you even don't have to change the query, it will use view automatically

Answer 2

add Indexes on Submission_Status and S.Submission_Date , Submission

Answer 3

select
COUNT (*)
from Submission S
where S.Submission_Status IN ('New','Quoted to Agent','Cleared')
and YEAR(S.Submission_Date) >= YEAR( GETDATE())
and S.Submission not in (select distinct Submission from Policy p FULL OUTER JOIN Quote q ON  p.Submission=q.Submission where Submission is not null)

Answer 4

you might use a LEFT OUTHER JOIN instead of using the Sub-Select checking if Submission==null

Basically you need to get the not matching records.

this link may help you to refactor your query

Retrieve non matching record using join

Answer 5

Change this part:

YEAR(S.Submission_Date) >= YEAR( GETDATE())

To:

S.Submission_Date >= DateAdd(Year, DateDiff(Year, 0, GETDATE()), 0)

This code will allow an index on Submission_Date to be used, if it exists. It is also functionally equivalent to your original statement.