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