Need to optimize not in sql statement

Question

I have an sql query:

select person from table t1
inner join person_history ph
on t1.person = ph.person
and t1.person not in (select person from person_history 
                      where effective_date < '01-01-2013')
and ph.person.effective_date > '01-01-2013'

since person_history contains a lot of records, this query is taking too long.

How can I optimize this code?

What is this: `and ph.person.effective_date > '01-01-2013'` that doesn't even seem like valid SQL. — Eric Petroelje, Dec 26 '12 at 20:51
Did you try to let your database `EXPLAIN` the query? That's usually a good way to find out why it's slow — ThiefMaster, Dec 26 '12 at 20:53
What type of SQL is this? Performance and tuning tends to be implementation-dependent. — RBarryYoung, Dec 26 '12 at 20:53
What is ph.person.effective_date? person in the select should have t1 or ph. — syed mohsin, Dec 26 '12 at 20:53
You select {person*history} that is not in history. Makes no sense to me. — wildplasser, Dec 26 '12 at 20:54
@syedmohsin: That can be legal in some dialects of SQL, such as postgres and mumps-based versions like Intersystems Cache`, I think. In fact, it might even be legal in SQL Server with UDTs (not sure). — RBarryYoung, Dec 26 '12 at 20:58
No. three level names can occur in Oracle and microsoft, I think. (Postgres maybe with remote connections) — wildplasser, Dec 26 '12 at 21:00

score 1 · Answer 1 · answered Dec 26 '12 at 20:54

1

Can't you just do something like this:

select person from table t1
inner join person_history ph
on t1.person = ph.person
where ph.effective_date >= '01-01-2013'

answered Dec 26 '12 at 20:54

Jonathan Wood

65,341
71
269
466

score 1 · Answer 2 · answered Dec 26 '12 at 21:25

You needn't exclude the NOT IN as it is already excluded in the WHERE Filter! the SQL will be simple as follows:

select person from table t1
inner join person_history ph on t1.person=ph.person
where effective_date > '01-01-2013'

Or:

select person from table t1
WHERE person IN(select ph.person from person_history ph
where effective_date > '01-01-2013' and t1.person=ph.person)

Need to optimize not in sql statement

2 Answers2