🚀 go-pugleaf

In article <36CE28AB.704C343B@nospam.mcbsys.com>, Mark Berry politely 
paraphrased ...
> After reading the posts here under the "Query troubles" topic, I'm
> wondering whether this whole thing may have to be re-written
> anyway--will I be able to delete the orphaned records if I ever get this
> JOIN to behave?
> 
If the result is readonly you won't be able to delete them, behaveing or 
not. I'm by no stretch of imagination an SQL guy, but this works for me, 
try it out.

close all
erase master.dbf
erase child.dbf
if not file("MASTER.DBF")
  create table "master.dbf" (order_id char(2))
  use "master.dbf"
  generate 10   //10 records from 01-10
  replace all order_id with str(recno(),2,0,"0")
  use
endif
if not file("CHILD.DBF")
  create table "child.dbf" (order_id char(2))
  use "child.dbf"
  generate 7   //7 record from 01-07
  replace all order_id with str(recno(),2,0,"0")
  use
endif

DELETE FROM MASTER M WHERE NOT M.ORDER_ID IN;
      (SELECT ORDER_ID FROM CHILD C WHERE c.ORDER_ID=M.ORDER_ID)
use master
//now 08.09 and 10 which where not in child.dbf
// are also deleted in the master
browse 

-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

On Fri, 19 Feb 1999 19:14:51 -0800, Mark Berry <markb@nospam.mcbsys.com>
wrote:

[...]

>I have an application that is supposed to maintain a 1:1 relation
>between two tables, Contact1 and Contact2, based on the AccountNo
>field.  Sometimes this gets out of whack, and I'm trying to write a
>little VDB7 routine to display (and ultimately delete) orphaned
>records.  The example below looks for Contact1 records with no matching
>Contact2.  
>
>The basic question is how to identify orphaned records with SQL.  My
>RIGHT OUTER JOIN checking a field in the "left-side" table for NULL
>doesn't seem to bring back any rows.

[...]

What version of the BDE are you using? I seem to recall a problem in some
earlier version using outer joins *and* filtering to just the non-matching
rows like this. Does it work if you juxtapose the two tables and use a LEFT
OUTER JOIN?

//////////////////////////////////////////////////////////////////////////
Steve Koterski                       "For people who like peace and quiet:
Technical Publications               a phoneless cord."
INPRISE Corporation                                             -- Unknown
http://www.inprise.com/delphi

Roamin,

Romain Strieff [TeamB] wrote:
> If the result is readonly you won't be able to delete them, behaveing or
> not. I'm by no stretch of imagination an SQL guy, but this works for me,
> try it out.
> 
> DELETE FROM MASTER M WHERE NOT M.ORDER_ID IN;
>       (SELECT ORDER_ID FROM CHILD C WHERE c.ORDER_ID=M.ORDER_ID)

Thanks, that works great on my sample data and makes sense to my simple
mind (select the good ones, then NOT them).  It also doesn't seem to
create the huge temporary tables created by a JOIN.  I was actually
testing with "SELECT *" in place of "DELETE", since I want to give the
user the opportunity to review the records before deleting them.

The problem comes when run on production-size tables.  I've got five
tables to check.  The master is about 4000 records; the child tables
range from 4000 to 55,000.  I finally killed the process after an hour. 
It CPU-bound, I think--NT Task Manager showed it running at 81% of my
Pentium Pro 200 the whole time, and I didn't hear a lot of disk
swapping.

I'm beginning to wonder if it wouldn't be simpler to just loop through
each table and do a findKey() based on each record.  WDYT?

Thanks much for your help!

Mark Berry

Steve,

Steve Koterski wrote:
> What version of the BDE are you using? I seem to recall a problem in some
> earlier version using outer joins *and* filtering to just the non-matching
> rows like this. Does it work if you juxtapose the two tables and use a LEFT
> OUTER JOIN?

I get the same results with 4.51 and 5.01, RIGHT or LEFT OUTER JOIN.

This is happening when Contact1 has 1 record and Contact2 has 0.  If I
add one more record to Contact1, an interesting thing happens:  the SQL
Results grid correctly displays both orphaned records until I scroll
right to look at other columns.  Then only the first record is visible;
the second row disappears (turns gray), although the indicator columns
still shows two rows.  If I try to scroll up, I see "1 of 2" briefly in
the upper right corner.  When I scroll back to the left, the second row
still is not visible.

Any ideas?  What are my alternatives?  Is the nested SELECT suggested by
Romain the way to go?  Why is that so slow?

Thanks very much for your help,

Mark Berry

In article <36D1B713.8E3E747B@nospam.mcbsys.com>, Mark Berry politely 
paraphrased ...
> 
> Thanks, that works great on my sample data and makes sense to my simple
> mind (select the good ones, then NOT them).  It also doesn't seem to
> create the huge temporary tables created by a JOIN.  I was actually
> testing with "SELECT *" in place of "DELETE", since I want to give the
> user the opportunity to review the records before deleting them.
> 
> The problem comes when run on production-size tables.  I've got five
> tables to check.  The master is about 4000 records; the child tables
> range from 4000 to 55,000.  I finally killed the process after an hour. 
> It CPU-bound, I think--NT Task Manager showed it running at 81% of my
> Pentium Pro 200 the whole time, and I didn't hear a lot of disk
> swapping.
---
No idea, are there _simple_ indexes for the involved fields?

> I'm beginning to wonder if it wouldn't be simpler to just loop through
> each table and do a findKey() based on each record.  WDYT?
---
That's also a possibility. 
-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--

I think I figured out how to get the join to work:  

- Put the table containing orphaned records on the left side of a LEFT
OUTER JOIN.

- Explicitly specify field names, i.e. don't use "SELECT *".

- Issue last() and first() before checking endOfSet.

I guessed at the last part after noticing that the SQL grid didn't
correctly display rows until you scrolled down, then back up.  (Does
news of such anomolies ever get back to Inprise?  Or this VDB still
being developed/debugged?)

Unfortunately, I'm unable to delete the rows based on this rowset, but
it may be the best way to display rows before deletion.

Mark Berry

Romain,

Romain Strieff [TeamB] wrote:
> No idea, are there _simple_ indexes for the involved fields?

No, but there are indexes that _begin_ with the involved field.  Adding
a simple index didn't affect performance.

I believe your earlier example could be simplified to omit the condition
within the subquery:

  DELETE FROM MASTER M WHERE NOT M.ORDER_ID IN;
      (SELECT ORDER_ID FROM CHILD C)

But even with that simplification, executing a SELECT with the "NOT IN
(subquery)" approach takes 1 minute 39 seconds on a medium-size table,
compared to 7 seconds for the "OUTER JOIN" approach.  And "delete for
not seek(key,alias)" takes 4 seconds.

I can only guess that the IN operator is not very efficient; maybe it
was tested more on short lists of values.  Too bad, as it does seem to
be the most elegant solution.

So I guess it's OUTER JOIN to get a rowset for displaying the records,
and "delete for not seek()" to do the actual deletion.  So much for
OODML.

Thanks again for your input.  I'm frustrated by the difficulties of
performing this simple operation, but I'm grateful for the SQL lesson!

Regards,

Mark Berry

In article <36D34BF6.997CBB03@nospam.mcbsys.com>, Mark Berry politely 
paraphrased ...
> 
> Romain Strieff [TeamB] wrote:
> > No idea, are there _simple_ indexes for the involved fields?
> No, but there are indexes that _begin_ with the involved field.  Adding
> a simple index didn't affect performance.

> 
> I believe your earlier example could be simplified to omit the condition
> within the subquery:
> 
>   DELETE FROM MASTER M WHERE NOT M.ORDER_ID IN;
>       (SELECT ORDER_ID FROM CHILD C)
> 
> But even with that simplification, executing a SELECT with the "NOT IN
> (subquery)" approach takes 1 minute 39 seconds on a medium-size table,
> compared to 7 seconds for the "OUTER JOIN" approach.  And "delete for
> not seek(key,alias)" takes 4 seconds.
>
> I can only guess that the IN operator is not very efficient; maybe it
> was tested more on short lists of values.  Too bad, as it does seem to
> be the most elegant solution.
> 
> So I guess it's OUTER JOIN to get a rowset for displaying the records,
> and "delete for not seek()" to do the actual deletion.  So much for
> OODML.
> 
> Thanks again for your input.  I'm frustrated by the difficulties of
> performing this simple operation, but I'm grateful for the SQL lesson!
You know more than I about this.<G> I hate the stuff.
-- 
Romain Strieff [TeamB]
Please read this newsgroup guide below before 
posting on the private Inprise newsgroups.
http://www.inprise.com/newsgroups/guide.html
--