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:
enter image description here

how does removing `distinct` accelerate your query? You don't need that... — Lukas Eder, Mar 02 '12 at 14:27
well I guess I need to remove these 2 lines completely so distinct is not important anyway — ilija veselica, Mar 02 '12 at 14:29

score 0 · Answer 1 · answered Mar 02 '12 at 14:29

0

add Indexes on Submission_Status and S.Submission_Date , Submission

answered Mar 02 '12 at 14:29

Zyku

1,429
2
23
37

score 0 · Answer 2 · edited Mar 02 '12 at 14:33

0

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)

edited Mar 02 '12 at 14:33

Korhan Ozturk

11,148
6
36
49

answered Mar 02 '12 at 14:30

Teja

13,214
36
93
155

I have tried it but results don't match my original query: http://i.stack.imgur.com/Kw7uC.jpg – ilija veselica Mar 02 '12 at 14:42

score 0 · Answer 3 · edited May 23 '17 at 10:32

0

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

edited May 23 '17 at 10:32

Community

1
1

answered Mar 02 '12 at 14:32

Massimiliano Peluso

26,379
6
61
70

George Mastros · Answer 4 · 2012-03-02T14:47:44.280

0

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.

edited Mar 02 '12 at 14:47

answered Mar 02 '12 at 14:40

George Mastros

24,112
4
51
59

score 0 · Answer 5 · answered Mar 02 '12 at 14:53

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

Select where not in another table

5 Answers5