Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!wuarchive!zaphod.mps.ohio-state.edu!sdd.hp.com!decwrl!csus.edu!ucdavis!csusac!unify!dmg
From: dmg@Unify.Com (Dave Glende)
Newsgroups: comp.databases
Subject: Re: a question regarding a query using Unify 4.0 SQL
Message-ID: <5l22xw2@Unify.Com>
Date: 15 Aug 90 17:38:40 GMT
References: <201603@hrc.UUCP>
Organization: Unify Corporation, Sacramento, CA, USA
Lines: 43

In article <201603@hrc.UUCP> dan@hrc.UUCP (Dan Troxel) writes:
>
>I am using Unify 4.0 with sql on a Convergent Tech. S/640.
>
>I have 2 tables.
>
>I wish to find all records in the parent table,
>that are *not* found in its child table.
>
>I believe this is wrong, at least *very* slow, but tell me what I have to do 
>to fix it.
>
>	select rolodex.entry_num from rolodex
>		where rolodex.referral_source = 'POST*'
>		and rolodex.entry_num ^=
>		select rolnotes.rol_linker from rolnotes
>			where rolnotes.rol_linker = rolodex.entry_num/
>-- 
>Dan Troxel @ Handwriting Research Corporation               WK 1-602-957-8870
>Camelback Corporate Center 2821 E. Camelback Road Suite 600 Phoenix, AZ 85016
>ncar!noao!asuvax!hrc!dan  zardoz!hrc!dan  hrc!dan@asuvax.eas.asu.edu

The major reason that it is *slow* is that the subquery is a correlated query
meaning that it must be executed for *every* row selected in the containing
select.

It seems that all that you really need to do is:

	select rolodex.entry_num from rolodex
		where rolodex.referral_source = 'POST*'
		and rolodex.entry_num ^=
		select rolnotes.rol_linker from rolnotes/


This should result in the subquery being executed only once and having
a temporary index built on the result.  If the number of rows resulting
from the subquery is large, you may need to increase TIMEM if you wish
to keep the index memory resident.
-- 

David Glende   Work:(916) 920-9092  | Unify Corporation
dmg@unify.com  FAX :(916) 921-5340  | 3870 Rosin Court 
                                    | Sacto, CA 95834