Add indexes to improve backup query performance.

3 files changed, 48 insertions, 26 deletions

diff --git a/Duplicati/Library/Main/Database/Database schema/12. Performance Indexes.sql b/Duplicati/Library/Main/Database/Database schema/12. Performance Indexes.sql
new file mode 100644
index 000000000..d39c493b6
--- /dev/null
+++ b/Duplicati/Library/Main/Database/Database schema/12. Performance Indexes.sql
@@ -0,0 +1,12 @@
+CREATE INDEX "nnc_Metadataset" ON Metadataset ("ID","BlocksetID")
+CREATE INDEX "nn_FilesetentryFile" on FilesetEntry ("FilesetID","FileID")
+
+-- Line 602 & 603 LocalBackupDatabase.cs 
+-- CREATE INDEX "tmpName1" ON "{0}" ("Path"),tmpName1
+-- CREATE INDEX "tmpName2" ON "{0}" ("Path"),tmpName2
+
+CREATE INDEX "nn_FileLookup_BlockMeta" ON FileLookup ("BlocksetID", "MetadataID")
+
+CREATE INDEX "nnc_BlocksetEntry" ON "BlocksetEntry" ("Index", "BlocksetID", "BlockID")
+
+UPDATE "Version" SET "Version" = 12;
\ No newline at end of file
diff --git a/Duplicati/Library/Main/Database/Database schema/Schema.sql b/Duplicati/Library/Main/Database/Database schema/Schema.sql
index 95e4529d3..2ee13404b 100644
--- a/Duplicati/Library/Main/Database/Database schema/Schema.sql
+++ b/Duplicati/Library/Main/Database/Database schema/Schema.sql
@@ -81,6 +81,8 @@ CREATE TABLE "FilesetEntry" (
 

 /* Improved reverse lookup for joining Fileset and File table */

 CREATE INDEX "FilesetentryFileIdIndex" on "FilesetEntry" ("FileID");

+CREATE INDEX "nn_FilesetentryFile" on FilesetEntry ("FilesetID","FileID");

+

 

 

 /*

@@ -109,6 +111,9 @@ CREATE TABLE "FileLookup" (
 

 /* Fast path based lookup, single properties are auto-indexed */

 CREATE UNIQUE INDEX "FileLookupPath" ON "FileLookup" ("PrefixID", "Path", "BlocksetID", "MetadataID");

+CREATE INDEX "nn_FileLookup_BlockMeta" ON FileLookup ("BlocksetID", "MetadataID");

+

+

 

 /*

 The File view contains an ID

@@ -167,7 +172,7 @@ CREATE TABLE "BlocksetEntry" (
 

 /* As this table is a cross table we need fast lookup */

 CREATE INDEX "BlocksetEntry_IndexIdsBackwards" ON "BlocksetEntry" ("BlockID");

-

+CREATE INDEX "nnc_BlocksetEntry" ON "BlocksetEntry" ("Index", "BlocksetID", "BlockID");

 

 /*

 The individual block hashes,

@@ -222,6 +227,8 @@ CREATE TABLE "Metadataset" (
 );

 

 CREATE INDEX "MetadatasetBlocksetID" ON "Metadataset" ("BlocksetID");

+CREATE INDEX "nnc_Metadataset" ON Metadataset ("ID","BlocksetID");

+

 

 /*

 Operations performed on the backend,

@@ -280,4 +287,4 @@ CREATE TABLE "ChangeJournalData" (
     "ConfigHash" TEXT NOT NULL  

 );

 

-INSERT INTO "Version" ("Version") VALUES (11);

+INSERT INTO "Version" ("Version") VALUES (12);

diff --git a/Duplicati/Library/Main/Database/LocalBackupDatabase.cs b/Duplicati/Library/Main/Database/LocalBackupDatabase.cs
index 10b8f737e..3f812d3ad 100644
--- a/Duplicati/Library/Main/Database/LocalBackupDatabase.cs
+++ b/Duplicati/Library/Main/Database/LocalBackupDatabase.cs
@@ -601,6 +601,9 @@ namespace Duplicati.Library.Main.Database
 
                     cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" AS " + subqueryFiles, tmpName1), lastFilesetId);
                     cmd.ExecuteNonQuery(string.Format(@"CREATE TEMPORARY TABLE ""{0}"" AS " + subqueryFiles, tmpName2), m_filesetId);
+                    cmd.ExecuteNonQuery(string.Format(@"CREATE INDEX ""nn_tmpName1"" ON ""{0}"" (""Path"")",tmpName1));
+                    cmd.ExecuteNonQuery(string.Format(@"CREATE INDEX ""nn_tmpName2"" ON ""{0}"" (""Path"")",tmpName2));
+
 
                     results.AddedFiles = cmd.ExecuteScalarInt64(string.Format(@"SELECT COUNT(*) FROM ""File"" INNER JOIN ""FilesetEntry"" ON ""File"".""ID"" = ""FilesetEntry"".""FileID"" WHERE ""FilesetEntry"".""FilesetID"" = ? AND ""File"".""BlocksetID"" != ? AND ""File"".""BlocksetID"" != ? AND NOT ""File"".""Path"" IN (SELECT ""Path"" FROM ""{0}"")", tmpName1), 0, m_filesetId, FOLDER_BLOCKSET_ID, SYMLINK_BLOCKSET_ID);
                     results.DeletedFiles = cmd.ExecuteScalarInt64(string.Format(@"SELECT COUNT(*) FROM ""{0}"" WHERE ""{0}"".""Path"" NOT IN (SELECT ""Path"" FROM ""File"" INNER JOIN ""FilesetEntry"" ON ""File"".""ID"" = ""FilesetEntry"".""FileID"" WHERE ""FilesetEntry"".""FilesetID"" = ?)", tmpName1), 0, m_filesetId);
@@ -691,9 +694,9 @@ namespace Duplicati.Library.Main.Database
         public void AppendFilesFromPreviousSetWithPredicate(System.Data.IDbTransaction transaction,
             Func<string, long, bool> exclusionPredicate, long fileSetId, long prevFileSetId, DateTime timestamp)
         {
-            if (exclusionPredicate == null)

-            {

-                AppendFilesFromPreviousSet(transaction, null, fileSetId, prevFileSetId, timestamp);

+            if (exclusionPredicate == null)
+            {
+                AppendFilesFromPreviousSet(transaction, null, fileSetId, prevFileSetId, timestamp);
                 return;
             }
 
@@ -704,7 +707,7 @@ namespace Duplicati.Library.Main.Database
                 long lastFilesetId = prevFileSetId < 0 ? GetPreviousFilesetID(cmd, timestamp, fileSetId) : prevFileSetId;
 
                 // copy entries from previous file set into a temporary table, except those file IDs already added by the current backup
-                var tempFileSetTable = "FilesetEntry-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());

+                var tempFileSetTable = "FilesetEntry-" + Library.Utility.Utility.ByteArrayAsHexString(Guid.NewGuid().ToByteArray());
                 cmd.Transaction = tr.Parent;
                 cmd.ExecuteNonQuery($@"CREATE TEMPORARY TABLE ""{tempFileSetTable}"" AS SELECT ""FileID"", ""Lastmodified"" FROM (SELECT DISTINCT ""FilesetID"", ""FileID"", ""Lastmodified"" FROM ""FilesetEntry"" WHERE ""FilesetID"" = ? AND ""FileID"" NOT IN (SELECT ""FileID"" FROM ""FilesetEntry"" WHERE ""FilesetID"" = ?))", lastFilesetId, fileSetId);
 
@@ -736,10 +739,10 @@ namespace Duplicati.Library.Main.Database
                 cmd.ExecuteNonQuery($@"INSERT INTO ""FilesetEntry"" (""FilesetID"", ""FileID"", ""Lastmodified"") 
                                       SELECT ?, ""FileID"", ""Lastmodified"" FROM ""{tempFileSetTable}""", fileSetId);
 
-                tr.Commit();

+                tr.Commit();
             }
-        }

-

+        }
+
         /// <summary>
         /// Creates a timestamped backup operation to correctly associate the fileset with the time it was created.
         /// </summary>
@@ -782,23 +785,23 @@ namespace Duplicati.Library.Main.Database
                     );
                 else
                     return default(RemoteVolumeEntry);
-        }

-

-        public IEnumerable<string> GetTemporaryFilelistVolumeNames(bool latestOnly, IDbTransaction transaction = null)

-        {

-            var incompleteFilesetIDs = GetIncompleteFilesets(transaction).OrderBy(x => x.Value).Select(x => x.Key).ToArray();

-

-            if (!incompleteFilesetIDs.Any())

-                return Enumerable.Empty<string>();

-

-            if (latestOnly)

-                incompleteFilesetIDs = new long[] { incompleteFilesetIDs.Last() };

-

-            var volumeNames = new List<string>();

-            foreach (var filesetID in incompleteFilesetIDs)

-                volumeNames.Add(GetRemoteVolumeFromFilesetID(filesetID).Name);

-

-            return volumeNames;

+        }
+
+        public IEnumerable<string> GetTemporaryFilelistVolumeNames(bool latestOnly, IDbTransaction transaction = null)
+        {
+            var incompleteFilesetIDs = GetIncompleteFilesets(transaction).OrderBy(x => x.Value).Select(x => x.Key).ToArray();
+
+            if (!incompleteFilesetIDs.Any())
+                return Enumerable.Empty<string>();
+
+            if (latestOnly)
+                incompleteFilesetIDs = new long[] { incompleteFilesetIDs.Last() };
+
+            var volumeNames = new List<string>();
+            foreach (var filesetID in incompleteFilesetIDs)
+                volumeNames.Add(GetRemoteVolumeFromFilesetID(filesetID).Name);
+
+            return volumeNames;
         }
 
         public IEnumerable<string> GetMissingIndexFiles(System.Data.IDbTransaction transaction)