Alternative to NOT IN
A user on IDS 9.40 had a slow anti-join: a NOT IN whose subquery was correlated (where B.id = A.id), forcing it to run once per row across a 9-million-row table. Suggestions: drop the correlation so the subquery is evaluated once; use a LEFT JOIN with WHERE b.id IS NULL; use MINUS (or INTERSECT for the IN case); and NOT EXISTS was noted to work despite the poster's belief otherwise. The poster reported the query then ran very fast, though he didn't say which form he used; it was also noted ANSI LEFT OUTER JOIN syntax only arrived in IDS 10.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
I am currently having an issue with a query. I have two huge tables, one
contains 9 million records the other 3 million records.
Informix version I am using is IDS 9.40C2
I have written a query:
select A.id
from tablea A
where A.id NOT IN
(select B.id
from tableb B
where B.id = A.id)
The tablea contains 9 million records, this would result in the inner query
being executed 9 million times.
IDS does not allow me to use NOT EXISTS.
Could you please let me know if there is a way to improve this query?
Thanks and Regards,
Naveen Naik
Try this:
SELECT a.id
FROM tableA a
LEFT JOIN tableB b
ON b.id = a.id
WHERE b.id IS NULL
--EEM
>-----Original Message-----
>From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
>NAVEEN NAIK
>Sent: Tuesday, January 18, 2011 11:02 AM
>To: ids@iiug.org
>Subject: Alternative to NOT IN [22469]
>
>Hi All,
>
>I am currently having an issue with a query. I have two huge tables, one
>contains 9 million records the other 3 million records.
>
>Informix version I am using is IDS 9.40C2
>
>I have written a query:
>
>select A.id
>from tablea A
>where A.id NOT IN
>(select B.id
>from tableb B
>where B.id = A.id)>
>The tablea contains 9 million records, this would result in the inner
>query being executed 9 million times.
>
>IDS does not allow me to use NOT EXISTS.
>
>Could you please let me know if there is a way to improve this query?
>
>Thanks and Regards,
>Naveen Naik
>
>
>************************************************************************
>*******
> Forum Note: Use "Reply" to post a response in the discussion forum.
Try something like this:
Select a.id
From table aMinus
Select b.id
From table b
This is typically faster with large tables and gets rid of that nasty temp
table that gets generated by the sub query.
I have multiple processes where we join very wide 200-500 million record
tables using this as opposed to the not in or in for that matter. For in
intersect is a great option.
Hope this helps.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of NAVEEN
NAIK
Sent: Tuesday, January 18, 2011 11:02 AM
To: ids@iiug.org
Subject: Alternative to NOT IN [22469]
Hi All,
I am currently having an issue with a query. I have two huge tables, one
contains 9 million records the other 3 million records.
Informix version I am using is IDS 9.40C2
I have written a query:
select A.id
from tablea A
where A.id NOT IN
(select B.id
from tableb B
where B.id = A.id)
The tablea contains 9 million records, this would result in the inner query
being executed 9 million times.
IDS does not allow me to use NOT EXISTS.
Could you please let me know if there is a way to improve this query?
Thanks and Regards,
Naveen Naik
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
On Tue, Jan 18, 2011 at 09:02, NAVEEN NAIK <navnaik@gmail.com> wrote:
> I am currently having an issue with a query. I have two huge tables, one
> contains 9 million records the other 3 million records.
>
> Informix version I am using is IDS 9.40C2
>
Time to upgrade - but you knew that.
> I have written a query:
>
> select A.id
> from tablea A
> where A.id NOT IN
> (select B.id
> from tableb B
> where B.id = A.id)>
> The tablea contains 9 million records, this would result in the inner query
> being executed 9 million times.
>
So, don't do it.
SELECT A.ID FROM TableA AS A
WHERE A.ID NOT IN (SELECT B.ID FROM TableB AS B);
This will generate the result set from the sub-query just once, because it
is not a correlated sub-query.
IDS does not allow me to use NOT EXISTS.
>
What do you mean?
SELECT A.ID FROM TableA AS A
WHERE NOT EXISTS(SELECT * FROM TableB AS B WHERE A.ID = B.ID)
I tested - admittedly on 11.70 but I'd expect it to work in 4.00 and all
points in between - the following query:
SELECT A.Atomic_Number FROM Elements AS A
WHERE NOT EXISTS(SELECT * FROM Isotopes AS B WHERE A.Atomic_Number =
B.Atomic_Number);
That's a correlated subquery again - probably not a good idea - but it
worked.
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--001636c5a78fa641cd049a22324b
Thanks for the help. I was trying for the LEFT OUTER JOIN, it gave me a syntax error, so i was under the assumption that this is not possible in Informix IDS. Now its running faster than a bullet :) Cheers, Naveen
On Tue, Jan 18, 2011 at 11:18, NAVEEN NAIK <navnaik@gmail.com> wrote: > Thanks for the help. > > I was trying for the LEFT OUTER JOIN, it gave me a syntax error, so i was > under the assumption that this is not possible in Informix IDS. > > Now its running faster than a bullet :) > Good - glad to hear something worked (but which version of the SQL statement are you using? The NOT EXISTS or the NOT IN?). Please remember that I don't have the context for what you chose - neither does anyone else. LEFT OUTER JOIN et al are a distressingly recent addition to IDS - one advantage of staying current is that you get useful new features such as this. AFAICR, it was added in IDS 10.00, and I've been very impressed with the implementation. I've pushed it hard and don't think I've found a bug in the code yet, despite some excruciating operations in nested queries, etc. A few times I thought I'd found something, but on more careful investigation, it turned out to be pilot error on my part - invalid SQL syntax, or not the answer I expected but the correct answer nonetheless. It has been rock solid in my experience. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2008.0513 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --001636c5c24112b2ea049a25c44b