dan@hrc.UUCP (Dan Troxel) (08/15/90)
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
dmg@Unify.Com (Dave Glende) (08/16/90)
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
allbery@NCoast.ORG (Brandon S. Allbery KB8JRR/KT) (08/16/90)
As quoted from <201603@hrc.UUCP> by dan@hrc.UUCP (Dan Troxel): +--------------- | 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/ +--------------- This joins every row in rolodex to every row in rolnotes *except* the ones which would join if the comparison were "=" in the subquery. What you want is: select rolodex.entry_num from rolodex where rolodex.referral_source = 'POST*' and 0 ^= select count(*) from rolnotes where rolnotes.rol_linker = rolodex.entry_num ; / (The above is the style we use at Telotech; your mileage, of course, will vary with respect to indentation, line breaks, optional syntax, etc.) This can be slow, but will be quite fast if there is an explicit relationship between rolodex.entry_num and rolnotes.rol_linker. ++Brandon -- Me: Brandon S. Allbery VHF: KB8JRR/KT on 220 (soon others) Internet: allbery@NCoast.ORG Delphi: ALLBERY uunet!usenet.ins.cwru.edu!ncoast!allbery America OnLine: KB8JRR
dan@hrc.UUCP (Dan Troxel) (08/17/90)
> select rolodex.entry_num from rolodex > where rolodex.referral_source = 'POST*' > and rolodex.entry_num ^= > select rolnotes.rol_linker from rolnotes/ How would the above know to select only null records, instead of all the records found without that entrynum. Example: select rolodex.entry_num,rolnotes.rol_linker from rolodex,rolnotes where rolodex.referral_source = 'POST*' and rolodex.entry_num ^= rolnotes.rol_linker/ Newsgroups: comp.databases -- 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
kak@Unify.Com (Kelly Kingdon) (08/17/90)
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 I think this will work better for you, it only executes the inner query once where as yours is being executed for every record it finds in the outer query (variable query): select rolodex.entry_num from rolodex where rolodex.referral_source = 'POST*' and not rolodex.entry_num in select rolnotes.rol_linker from rolnotes/ -- Kelly Kingdon internet: kak@unify.UUCP Premium Customer Support ...!{csusac,pyramid}!unify!kak Unify Corporation voice: (916) 922-1177 3870 Rosin Court, Sacramento, CA 95834 fax: (916) 920-5306