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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users