Re: [Bacula-users] Performance with MySQL queries since 3.0.0 (Dir inserting attributes hang)
2009-06-19 07:16:08
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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
|
|