I have a test job with 15,764,45 rows in the Batch table. With a change in SQL, I went from 45 minutes to 15 minutes.
Old SQL:
SELECT batch_testing.FileIndex, batch_testing.JobId, Path.PathId,
Filename.FilenameId, batch_testing.LStat,
batch_testing.MD5, batch_testing.DeltaSeq
FROM batch_testing JOIN Path ON (batch_testing.Path = Path.Path)
JOIN Filename ON (batch_testing.Name = Filename.Name);
New SQL:
explain analyze
INSERT INTO File (FileIndex, JobId, PathId, FilenameId, LStat, MD5, DeltaSeq)
SELECT BT.FileIndex,
BT.JobId,
(SELECT Path.PathId FROM Path WHERE Path.path =
BT.Path),
(SELECT Filename.FilenameId FROM Filename WHERE Filename.name =
BT.Name) ,
BT.LStat,
BT.MD5,
BT.DeltaSeq
FROM batch_testing BT;
NOTE: I have used the table Batch_Testing... because I was testing. :)
https://gist.github.com/dlangille/58a414a260f4c6706d7e
<https://gist.github.com/dlangille/58a414a260f4c6706d7e>
Hope this helps.
signature.asc
Description: Message signed with OpenPGP using GPGMail
------------------------------------------------------------------------------ Site24x7 APM Insight: Get Deep Visibility into Application Performance APM + Mobile APM + RUM: Monitor 3 App instances at just $35/Month Monitor end-to-end web transactions and take corrective actions now Troubleshoot faster and improve end-user experience. Signup Now! http://pubads.g.doubleclick.net/gampad/clk?id=267308311&iu=/4140
_______________________________________________ Bacula-devel mailing list Bacula-devel@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-devel
