Hello,
I'm still waiting for my test database to fill up with Eric's data (actually,
it's full now, but generating the right indexes is taking lots of time).
But, I have another proposed solution, better than the last one I made.
My previous solution was still taking a very very long time for a backup of a
particular client that I had. Removing mention of BaseFiles did not help for
this client.
However, the following did, and it doesn't break Base jobs.
Eric, I would appreciate it if you could give this a go on your test machine.
It removes nasty the join on JobTDate by replacing it with a join on JobId
(which makes more sense, and is also an index on Job and File).
This also means it can get rid of the outer WHERE 'BaseJobId' OR 'JobId' that I
was complaining about before.
The correct JobId is chosen with MAX(JobTDate) by ordering by JobTDate DESC on
the innermost select.
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 JobId, MAX(JobTDate) AS JobTDate, PathId, FilenameId
FROM
( SELECT JobId, JobTDate, PathId, FilenameId
FROM File
JOIN Job USING (JobId)
WHERE File.JobId IN ($jobids)
UNION ALL
SELECT BaseJobId, JobTDate, PathId, FilenameId
FROM BaseFiles
JOIN File USING (FileId)
JOIN Job ON (BaseJobId = Job.JobId)
WHERE BaseFiles.JobId IN ($jobids)
ORDER BY JobTDate DESC
)
AS tmp GROUP BY PathId, FilenameId
) AS T1
WHERE
T1.JobId = Job.JobId
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 Path.Path, Filename.Name, Temp.JobId 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
