Mercurial > hg > orthanc

changeset 5866:5c484b4f99d5 find-refactoring tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
trying to speed up count of children
author Alain Mazy <am@orthanc.team>
date Wed, 06 Nov 2024 19:25:30 +0100
parents 083dac252b01
children
files OrthancServer/Plugins/Engine/OrthancPluginDatabaseV4.cpp OrthancServer/Plugins/Include/orthanc/OrthancDatabasePlugin.proto OrthancServer/Sources/Database/FindRequest.h OrthancServer/Sources/Database/FindResponse.h OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp OrthancServer/Sources/ResourceFinder.cpp
diffstat 6 files changed, 140 insertions(+), 10 deletions(-) [+]
line wrap: on
line diff
--- a/OrthancServer/Plugins/Engine/OrthancPluginDatabaseV4.cpp	Wed Nov 06 08:51:02 2024 +0100
+++ b/OrthancServer/Plugins/Engine/OrthancPluginDatabaseV4.cpp	Wed Nov 06 19:25:30 2024 +0100
@@ -291,6 +291,7 @@
                       const FindRequest::ChildrenSpecification& source)
   {
     target.set_retrieve_identifiers(source.IsRetrieveIdentifiers());
+    target.set_retrieve_count(source.IsRetrieveCount());
 
     for (std::set<MetadataType>::const_iterator it = source.GetMetadata().begin(); it != source.GetMetadata().end(); ++it)
     {
@@ -333,6 +334,8 @@
       target.AddChildIdentifier(level, source.identifiers(i));
     }
 
+    target.SetChildrenCount(level, source.count());
+
     for (int i = 0; i < source.main_dicom_tags().size(); i++)
     {
       const DicomTag tag(source.main_dicom_tags(i).group(), source.main_dicom_tags(i).element());
--- a/OrthancServer/Plugins/Include/orthanc/OrthancDatabasePlugin.proto	Wed Nov 06 08:51:02 2024 +0100
+++ b/OrthancServer/Plugins/Include/orthanc/OrthancDatabasePlugin.proto	Wed Nov 06 19:25:30 2024 +0100
@@ -877,6 +877,7 @@
       bool retrieve_identifiers = 1;
       repeated int32 retrieve_metadata = 2;
       repeated Tag retrieve_main_dicom_tags = 3;
+      bool retrieve_count = 4;
     }
     message Ordering {
       OrderingKeyType key_type = 1;
@@ -945,6 +946,7 @@
       repeated string identifiers = 1;
       repeated MultipleTags main_dicom_tags = 2;
       repeated MultipleMetadata metadata = 3;
+      uint64 count = 4;
     }
 
     int64 internal_id = 1;
--- a/OrthancServer/Sources/Database/FindRequest.h	Wed Nov 06 08:51:02 2024 +0100
+++ b/OrthancServer/Sources/Database/FindRequest.h	Wed Nov 06 19:25:30 2024 +0100
@@ -185,10 +185,12 @@
       bool                    identifiers_;
       std::set<MetadataType>  metadata_;
       std::set<DicomTag>      mainDicomTags_;
+      bool                    count_;
 
     public:
       ChildrenSpecification() :
-        identifiers_(false)
+        identifiers_(false),
+        count_(false)
       {
       }
 
@@ -202,6 +204,16 @@
         return identifiers_;
       }
 
+      void SetRetrieveCount(bool retrieve)
+      {
+        count_ = retrieve;
+      }
+
+      bool IsRetrieveCount() const
+      {
+        return count_;
+      }
+
       void AddMetadata(MetadataType metadata)
       {
         metadata_.insert(metadata);
--- a/OrthancServer/Sources/Database/FindResponse.h	Wed Nov 06 08:51:02 2024 +0100
+++ b/OrthancServer/Sources/Database/FindResponse.h	Wed Nov 06 19:25:30 2024 +0100
@@ -73,10 +73,16 @@
       typedef std::map<DicomTag, std::set<std::string>* >      MainDicomTagValues;
 
       std::set<std::string>  identifiers_;
+      uint64_t               count_;
       MetadataValues         metadataValues_;
       MainDicomTagValues     mainDicomTagValues_;
 
     public:
+      ChildrenInformation()
+      : count_(0)
+      {
+      }
+
       ~ChildrenInformation();
 
       void AddIdentifier(const std::string& identifier);
@@ -86,6 +92,16 @@
         return identifiers_;
       }
 
+      void SetCount(uint64_t count)
+      {
+        count_ = count;
+      }
+
+      uint64_t GetCount() const
+      {
+        return count_;
+      }
+
       void AddMetadataValue(MetadataType metadata,
                             const std::string& value);
 
@@ -272,6 +288,17 @@
         return GetChildrenInformation(level).GetIdentifiers();
       }
 
+      void SetChildrenCount(ResourceType level,
+                            uint64_t count)
+      {
+        GetChildrenInformation(level).SetCount(count);
+      }
+
+      uint64_t GetChildrenCount(ResourceType level) const
+      {
+        return GetChildrenInformation(level).GetCount();
+      }
+
       void AddChildrenMetadataValue(ResourceType level,
                                     MetadataType metadata,
                                     const std::string& value)
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Wed Nov 06 08:51:02 2024 +0100
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Wed Nov 06 19:25:30 2024 +0100
@@ -491,10 +491,13 @@
 #define QUERY_CHILDREN_IDENTIFIERS 20
 #define QUERY_CHILDREN_MAIN_DICOM_TAGS 21
 #define QUERY_CHILDREN_METADATA 22
+#define QUERY_CHILDREN_COUNT 23
 #define QUERY_GRAND_CHILDREN_IDENTIFIERS 30
 #define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31
 #define QUERY_GRAND_CHILDREN_METADATA 32
+#define QUERY_GRAND_CHILDREN_COUNT 33
 #define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40
+#define QUERY_GRAND_GRAND_CHILDREN_COUNT 41
 #define QUERY_ONE_INSTANCE_IDENTIFIER 50
 #define QUERY_ONE_INSTANCE_METADATA 51
 #define QUERY_ONE_INSTANCE_ATTACHMENTS 52
@@ -911,6 +914,25 @@
                "FROM Lookup "
                "  INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId ";
       }
+      // no need to count if we have retrieved the list of identifiers
+      else if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Study).IsRetrieveCount()) ||
+          (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveCount()) ||
+          (requestLevel == ResourceType_Series && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveCount()))
+      {
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  NULL AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  COUNT(*) AS c6_int1, "
+               "  NULL AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "FROM Lookup "
+               "  INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId GROUP BY Lookup.internalId ";
+      }
 
       // need grandchildren identifiers ?
       if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
@@ -931,6 +953,25 @@
               "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
               "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
       }
+      // no need to count if we have retrieved the list of identifiers
+      else if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveCount()) ||
+          (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveCount()))
+      {
+        sql += "UNION SELECT "
+              "  " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+              "  Lookup.internalId AS c1_internalId, "
+              "  NULL AS c2_rowNumber, "
+              "  NULL AS c3_string1, "
+              "  NULL AS c4_string2, "
+              "  NULL AS c5_string3, "
+               "  COUNT(*) AS c6_int1, "
+              "  NULL AS c7_int2, "
+              "  NULL AS c8_big_int1, "
+              "  NULL AS c9_big_int2 "
+              "FROM Lookup "
+              "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
+              "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId GROUP BY Lookup.internalId ";
+      }
 
       // need grandgrandchildren identifiers ?
       if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())
@@ -951,6 +992,25 @@
               "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
               "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
       }
+      // no need to count if we have retrieved the list of identifiers
+      else if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveCount())
+      {
+        sql += "UNION SELECT "
+              "  " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+              "  Lookup.internalId AS c1_internalId, "
+              "  NULL AS c2_rowNumber, "
+              "  NULL AS c3_string1, "
+              "  NULL AS c4_string2, "
+              "  NULL AS c5_string3, "
+               "  COUNT(*) AS c6_int1, "
+              "  NULL AS c7_int2, "
+              "  NULL AS c8_big_int1, "
+              "  NULL AS c9_big_int2 "
+              "FROM Lookup "
+              "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
+              "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
+              "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId GROUP BY Lookup.internalId ";
+      }
 
 
       sql += " ORDER BY c0_queryId, c2_rowNumber";  // this is really important to make sure that the Lookup query is the first one to provide results since we use it to create the responses element !
@@ -983,10 +1043,10 @@
           case QUERY_ATTACHMENTS:
           {
             FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
-            FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)), 
-                          s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C4_STRING_2),
+            FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)),
+                          s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C4_STRING_2),
                           static_cast<CompressionType>(s.ColumnInt(C7_INT_2)),
-                          s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C5_STRING_3));
+                          s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C5_STRING_3));
             res.AddAttachment(file, 0 /* TODO - REVISIONS */);
           }; break;
 
@@ -1084,6 +1144,8 @@
             FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
             res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1),
                                    s.ColumnString(C3_STRING_1));
+            res.SetChildrenCount(static_cast<ResourceType>(requestLevel + 1),
+                                 res.GetChildrenIdentifiers(static_cast<ResourceType>(requestLevel + 1)).size());
           }; break;
 
           case QUERY_GRAND_CHILDREN_IDENTIFIERS:
@@ -1091,6 +1153,8 @@
             FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
             res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2),
                                    s.ColumnString(C3_STRING_1));
+            res.SetChildrenCount(static_cast<ResourceType>(requestLevel + 2),
+                                 res.GetChildrenIdentifiers(static_cast<ResourceType>(requestLevel + 2)).size());
           }; break;
 
           case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS:
@@ -1098,6 +1162,29 @@
             FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
             res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 3),
                                    s.ColumnString(C3_STRING_1));
+            res.SetChildrenCount(static_cast<ResourceType>(requestLevel + 3),
+                                 res.GetChildrenIdentifiers(static_cast<ResourceType>(requestLevel + 3)).size());
+          }; break;
+
+          case QUERY_CHILDREN_COUNT:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.SetChildrenCount(static_cast<ResourceType>(requestLevel + 1),
+                                 static_cast<uint64_t>(s.ColumnInt64(C6_INT_1)));
+          }; break;
+
+          case QUERY_GRAND_CHILDREN_COUNT:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.SetChildrenCount(static_cast<ResourceType>(requestLevel + 2),
+                                 static_cast<uint64_t>(s.ColumnInt64(C6_INT_1)));
+          }; break;
+
+          case QUERY_GRAND_GRAND_CHILDREN_COUNT:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.SetChildrenCount(static_cast<ResourceType>(requestLevel + 3),
+                                 static_cast<uint64_t>(s.ColumnInt64(C6_INT_1)));
           }; break;
 
           case QUERY_ONE_INSTANCE_IDENTIFIER:
@@ -1115,10 +1202,10 @@
           case QUERY_ONE_INSTANCE_ATTACHMENTS:
           {
             FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
-            FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)), 
-                          s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C4_STRING_2),
+            FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)),
+                          s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C4_STRING_2),
                           static_cast<CompressionType>(s.ColumnInt(C7_INT_2)),
-                          s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C5_STRING_3));
+                          s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C5_STRING_3));
             res.AddOneInstanceAttachment(file);
           }; break;
 
--- a/OrthancServer/Sources/ResourceFinder.cpp	Wed Nov 06 08:51:02 2024 +0100
+++ b/OrthancServer/Sources/ResourceFinder.cpp	Wed Nov 06 19:25:30 2024 +0100
@@ -56,7 +56,7 @@
     if (request_.GetLevel() == parentLevel)
     {
       requestedComputedTags_.insert(tag);
-      request_.GetChildrenSpecification(childLevel).SetRetrieveIdentifiers(true);
+      request_.GetChildrenSpecification(childLevel).SetRetrieveCount(true);
     }
   }
 
@@ -68,8 +68,7 @@
   {
     if (IsRequestedComputedTag(tag))
     {
-      const std::set<std::string>& children = resource.GetChildrenIdentifiers(level);
-      requestedTags.SetValue(tag, boost::lexical_cast<std::string>(children.size()), false);
+      requestedTags.SetValue(tag, boost::lexical_cast<std::string>(resource.GetChildrenCount(level)), false);
     }
   }