diff options
author | warwickmm <warwickmm@users.noreply.github.com> | 2022-03-03 08:10:16 +0300 |
---|---|---|
committer | GitHub <noreply@github.com> | 2022-03-03 08:10:16 +0300 |
commit | fd1e687ac8a5a872fd15b88633c3f95c00a9e4f9 (patch) | |
tree | 2d13f5eb7bf64fa578fb9d3a49e2b122122b9e37 | |
parent | 781347d4a4bdf0352aa25ed275f8bb9f4d248350 (diff) | |
parent | d44496f6d4fc7c62d349caf1cf55977de191ad1a (diff) |
Merge pull request #4687 from jedthe3rd/db_changes
Add indexes to improve backup query performance
4 files changed, 49 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..9301b637f --- /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; 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) diff --git a/Duplicati/Library/Main/Duplicati.Library.Main.csproj b/Duplicati/Library/Main/Duplicati.Library.Main.csproj index ffc355847..65333820d 100644 --- a/Duplicati/Library/Main/Duplicati.Library.Main.csproj +++ b/Duplicati/Library/Main/Duplicati.Library.Main.csproj @@ -207,6 +207,7 @@ <EmbeddedResource Include="Database\Database schema\9. Refactor Paths.sql" />
<EmbeddedResource Include="Database\Database schema\10. Add IsFullBackup to Fileset table.sql" />
<EmbeddedResource Include="Database\Database schema\11. Add Block indices.sql" />
+ <EmbeddedResource Include="Database\Database schema\12. Performance Indexes.sql" />
</ItemGroup>
<Import Project="$(MSBuildBinPath)\Microsoft.CSharp.targets" />
<!-- To modify your build process, add your task inside one of the targets below and uncomment it.
|