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