On Wed, Apr 07, 2010 at 02:51:42PM +0100, Martin Simmons wrote:
> Does it still run quickly if keep that Job.JobId IN clause but use the numbers
> returned by
> 
> SELECT DISTINCT BaseJobId
>  FROM BaseFiles
>  WHERE JobId IN (22,23,31,34,42,48,52)
> 
> in place of the the nested select?

In my case, there are no numbers returned.
But, I assume that you are suggesting something like the command below.
For me, it completes in under a second.

SELECT Path.Path, Filename.Name, Temp.FileIndex, Temp.JobId, LStat, MD5
FROM
   ( SELECT FileId, Job.JobId AS JobId, FileIndex, File.PathId AS PathId,
        File.FilenameId AS FilenameId, LStat, MD5
     FROM Job, File,
            ( SELECT MAX(JobTDate) AS JobTDate, PathId, FilenameId
              FROM
                  ( SELECT JobTDate, PathId, FilenameId
                    FROM File
                    JOIN Job USING (JobId)
                    WHERE File.JobId IN (22,23,31,34,42,48,52)
                    UNION ALL
                    SELECT JobTDate, PathId, FilenameId
                    FROM BaseFiles
                    JOIN File USING (FileId)
                    JOIN Job  ON    (BaseJobId = Job.JobId)
                    WHERE BaseFiles.JobId IN (22,23,31,34,42,48,52)
                  )
              AS tmp GROUP BY PathId, FilenameId
            ) AS T1
     WHERE
        ( Job.JobId IN (22,23,31,34,42,48,52)
     OR   Job.JobId IN (22,23,31,34,42,48,52) )
     AND T1.JobTDate = Job.JobTDate
     AND Job.JobId = File.JobId
     AND T1.PathId = File.PathId
     AND T1.FilenameId = File.FilenameId
   ) AS Temp
JOIN Filename ON (Filename.FilenameId = Temp.FilenameId)
JOIN Path ON (Path.PathId = Temp.PathId)
WHERE FileIndex > 0
ORDER BY Temp.JobId, FileIndex ASC



------------------------------------------------------------------------------
Download Intel® Parallel Studio Eval
Try the new software tools for yourself. Speed compiling, find bugs
proactively, and fine-tune applications for parallel performance.
See why Intel Parallel Studio got high marks during beta.
http://p.sf.net/sfu/intel-sw-dev
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users