Mike Holden wrote: > Jari Fredriksson wrote: > >>> INSERT INTO Filename( Name ) >>> SELECT a.Name >>> FROM ( >>> >>> SELECT DISTINCT Name >>> FROM batch >>> ) AS a >>> WHERE NOT >>> EXISTS ( >>> >>> SELECT Name >>> FROM Filename AS f >>> WHERE f.Name = a.Name >>> ) >>> >>> >> That looks silly. >> >> I would write it shorter as >> >> 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! > Good suggestions, sounds like there might be an overall performance problem with the current query for batch-inserts with lots of data. I'm a bit unsure if I dare test these queries on my current installation.
I'll CC Kern on the thread, perhaps he has some insights. // Tom ------------------------------------------------------------------------------ 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