Tom Sommer wrote: > 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. > So this morning I had to kill the above query because it's been running for 24+ hours, preventing the new daily jobs from running. I think I'm going to try and disable batch-inserts, the current situation is simply not good enough, it's become a major headache to run backups suddenly. When I hit the end of this month and ALL servers have to run FULL backups, I'm gonna be in a world of trouble I think - I just don't understand what has changed, because it's all been running great up until now.
// Tom ------------------------------------------------------------------------------ Are you an open source citizen? Join us for the Open Source Bridge conference! Portland, OR, June 17-19. Two days of sessions, one day of unconference: $250. Need another reason to go? 24-hour hacker lounge. Register today! http://ad.doubleclick.net/clk;215844324;13503038;v?http://opensourcebridge.org _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
