🚀 go-pugleaf

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
--