> James Harper wrote:
>> 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
> 
> True, but if the outer query contains a very large number
> of records, the inner query is executed over and over and
> over and ... 
> 
> This multiplies the small execution time by a large
> number. 
> 
> Think of it like a WHILE loop that executes a block of
> code each time round. The inner block of code might be
> quick, but if the loop goes round several million times,
> the cumulative time becomes significant. 
> 
> One general rule of thumb of SQL execution is that bulk
> processing is almost always faster than processing
> records individually in a loop. 

All right then. Then there is this version:

INSERT INTO Filename(Name)
SELECT DISTINCT Name
FROM batch AS
WHERE Name NOT IN
(
    SELECT DISTINCT Name
    FROM Filename
);

I guess it depends on RDMS, but I wonder it it executes the subquery more than 
once.



------------------------------------------------------------------------------
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