# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1748856660 -7200
# Node ID 933a549de31fa29fdc9c6115bc0dd3fff730eac7
# Parent  d6f9862022360ac61bf500c69011d4ceffbfe68d
delayed deletion plugin: add an index to speed up performance

diff -r d6f986202236 -r 933a549de31f NEWS
--- a/NEWS	Fri May 30 22:03:32 2025 +0200
+++ b/NEWS	Mon Jun 02 11:31:00 2025 +0200
@@ -9,6 +9,14 @@
   - If "LimitMainDicomTagsReconstructLevel" was set, files were not transcoded if they had to.
     The "LimitMainDicomTagsReconstructLevel" configuration is now ignored when a full processing
     is required.
+* Delayed Deletion plugin:
+  - Added an index in the delayed-deletion SQLite external DB to speed up delayed deletions.
+    This new index will only apply to new databases.  If you wish to speed up an existing installation,
+    run "CREATE INDEX PendingIndex ON Pending(uuid)" manually in the plugin SQLite DB.
+    Patch provided by Yurii (George) from ivtech.dev.
+    With this patch, we observed a 100 fold performance improvement when the 
+    "Pending" table contains 1-2 millions files.
+      
 * Configuration options "RejectSopClasses" and "RejectedSopClasses" are taken as synonyms.
   In Orthanc 1.12.6 and 1.12.7, "RejectSopClasses" was used instead of the expected
   "RejectedSopClasses" spelling.
diff -r d6f986202236 -r 933a549de31f OrthancServer/Plugins/Samples/DelayedDeletion/PendingDeletionsDatabase.cpp
--- a/OrthancServer/Plugins/Samples/DelayedDeletion/PendingDeletionsDatabase.cpp	Fri May 30 22:03:32 2025 +0200
+++ b/OrthancServer/Plugins/Samples/DelayedDeletion/PendingDeletionsDatabase.cpp	Mon Jun 02 11:31:00 2025 +0200
@@ -42,6 +42,12 @@
     if (!db_.DoesTableExist("Pending"))
     {
       db_.Execute("CREATE TABLE Pending(uuid TEXT, type INTEGER)");
+      
+      // New in v 1.12.7+
+      // add an index on uuid to speed up the DELETE FROM Pending WHERE uuid=?
+      // With this patch, we observed a 100 fold performance
+      // improvement when the Pending table contains 1-2 millions files.
+      db_.Execute("CREATE INDEX PendingIndex ON Pending(uuid)");
     }
     
     t.Commit();