Re: Help: SQL .*Not IN* alternatives

> Date: Fri, 4 Nov 1994 11:19:53 -0500 > From: DONALD_BOOTHBY@IMA.ISD.STATE.IN.US > Subject: Help: SQL .*Not IN* alternatives > To: informix-list@rmy.emory.edu > > I have a user with two flat files who is trying to find data in one > table that is not in the other. > > IDMS (6,000 rows) NCS (260,000 rows) > table table > -------------- ----------- ... example details omitted ... > > He used the following SQL: > SELECT DISTINCT "idms"."ssn", "idms"."lastname", "idms"."firstname", > "idms"."mi", "idms"."ssaciid", "idms"."college", "idms"."initials" > FROM "idms", "ncs" > WHERE ( "idms"."ssn" NOT IN (SELECT DISTINCT "ncs"."ssn" from > "ncs")) > ORDER BY "idms"."college" ASC> > but since these files are large, it was still chugging along 4 hours > after the query was started. ... more stuff omitted ... > > TIA, > Don Boothby (donald_boothby@ima.isd.state.in.us) > Try to avoid NOT IN unless the table searched is *really* small. As written, this query will cause the 260,000 row "ncs" table to be searched 6,000 times. No wonder it is slow. *Much* better is: SELECT <columns> FROM idms INTO TEMP <temptable>; DELETE FROM <temptable> WHERE ssn IN ( SELECT ssn FROM ncs ); This will only search the ncs table one time, giving you a 6,000-fold speed increase! Regards, Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\