> > INSERT INTO Filename(Name) > > SELECT DISTINCT Name > > FROM batch AS a > > WHERE NOT EXISTS > > ( > > SELECT Name > > FROM Filename AS f > > WHERE f.Name = a.Name > > ) > > You may also want to consider using a JOIN rather than a subquery with a > NOT EXISTS, something like (untested and unvalidated!): > > INSERT INTO filename(name) > SELECT DISTINCT name > FROM batch AS a > LEFT JOIN filename AS f USING (name) > WHERE f.name IS NULL > > I know from experience as an Oracle DBA (my day job) that this can often > produce far more efficient results. > > Of course, all options need testing for both speed of execution and > resource usage, bearing in mind that data varies from one installation to > another, and one size may not fit all!
The subquery returns a very small result set (0 or 1, assuming you use DISTINCT) and so isn't too inefficient. It's when you say 'WHERE NOT EXISTS (SOME QUERY WITH LOTS OF RESULTS)' that you start to really bog down James ------------------------------------------------------------------------------ Crystal Reports - New Free Runtime and 30 Day Trial Check out the new simplified licensing option that enables unlimited royalty-free distribution of the report engine for externally facing server and web deployment. http://p.sf.net/sfu/businessobjects _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
