# HG changeset patch # User Alain Mazy # Date 1687450061 -7200 # Node ID 7e123f047771057204bf5f0fbf8cbe44cd8a125c # Parent f2d3b5c5a68d52a3500a8bb55f4cc25c8da7b355 LookupResources optimization continued diff -r f2d3b5c5a68d -r 7e123f047771 Framework/Plugins/IndexBackend.cpp --- a/Framework/Plugins/IndexBackend.cpp Thu Jun 22 12:15:48 2023 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Thu Jun 22 18:07:41 2023 +0200 @@ -2063,46 +2063,79 @@ void IndexBackend::LookupResources(IDatabaseBackendOutput& output, DatabaseManager& manager, const std::vector& lookup, - OrthancPluginResourceType queryLevel, + OrthancPluginResourceType queryLevel_, const std::set& labels, Orthanc::LabelsConstraint labelsConstraint, uint32_t limit, bool requestSomeInstance) { LookupFormatter formatter(manager.GetDialect()); + Orthanc::ResourceType queryLevel = Orthanc::Plugins::Convert(queryLevel_); + Orthanc::ResourceType lowerLevel, upperLevel; + Orthanc::ISqlLookupFormatter::GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); std::string sql; bool enableNewStudyCode = true; - if (enableNewStudyCode && queryLevel == OrthancPluginResourceType_Study) + if (enableNewStudyCode && lowerLevel == queryLevel && upperLevel == queryLevel) { - // separate path for the studies since it has been specifically optimized - Orthanc::ISqlLookupFormatter::ApplyExperimental(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel), labels, labelsConstraint, limit); - - if (requestSomeInstance) - { - sql = ("SELECT studies_series.studies_public_id, MIN(instances.publicId) AS instances_public_id " - "FROM (SELECT studies.publicId AS studies_public_id, MIN(series.internalId) AS series_internal_id " - "FROM (" + sql + - ") AS studies " - "INNER JOIN Resources series ON series.parentId = studies.internalId " - "GROUP BY studies.publicId " - ") AS studies_series " - "INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id " - "GROUP BY studies_series.studies_public_id"); - } - } - else - { - Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel), - labels, labelsConstraint, limit); + Orthanc::ISqlLookupFormatter::ApplySingleLevel(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit); if (requestSomeInstance) { // Composite query to find some instance if requested switch (queryLevel) { - case OrthancPluginResourceType_Patient: + case Orthanc::ResourceType_Patient: + sql = ("SELECT patients_studies.patients_public_id, MIN(instances.publicId) AS instances_public_id " + "FROM (SELECT patients.publicId AS patients_public_id, MIN(studies.internalId) AS studies_internal_id " + "FROM (" + sql + + ") AS patients " + "INNER JOIN Resources studies ON studies.parentId = patients.internalId " + "GROUP BY patients.publicId " + ") AS patients_studies " + "INNER JOIN Resources series ON series.parentId = patients_studies.studies_internal_id " + "INNER JOIN Resources instances ON instances.parentId = series.internalId " + "GROUP BY patients_studies.patients_public_id"); + break; + case Orthanc::ResourceType_Study: + sql = ("SELECT studies_series.studies_public_id, MIN(instances.publicId) AS instances_public_id " + "FROM (SELECT studies.publicId AS studies_public_id, MIN(series.internalId) AS series_internal_id " + "FROM (" + sql + + ") AS studies " + "INNER JOIN Resources series ON series.parentId = studies.internalId " + "GROUP BY studies.publicId " + ") AS studies_series " + "INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id " + "GROUP BY studies_series.studies_public_id"); + break; + case Orthanc::ResourceType_Series: + sql = ("SELECT series.publicId AS series_public_id, MIN(instances.publicId) AS instances_public_id " + "FROM (" + sql + + ") AS series " + "INNER JOIN Resources instances ON instances.parentId = series.internalId " + "GROUP BY series.publicId "); + break; + + case Orthanc::ResourceType_Instance: + sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances"); + break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + } + } + else + { + Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit); + + if (requestSomeInstance) + { + // Composite query to find some instance if requested + switch (queryLevel) + { + case Orthanc::ResourceType_Patient: sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients " "INNER JOIN Resources studies ON studies.parentId = patients.internalId " "INNER JOIN Resources series ON series.parentId = studies.internalId " @@ -2110,19 +2143,19 @@ "GROUP BY patients.publicId"); break; - case OrthancPluginResourceType_Study: + case Orthanc::ResourceType_Study: sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies " "INNER JOIN Resources series ON series.parentId = studies.internalId " "INNER JOIN Resources instances ON instances.parentId = series.internalId " "GROUP BY studies.publicId"); break; - case OrthancPluginResourceType_Series: + case Orthanc::ResourceType_Series: sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series " "INNER JOIN Resources instances ON instances.parentId = series.internalId " "GROUP BY series.publicId"); break; - case OrthancPluginResourceType_Instance: + case Orthanc::ResourceType_Instance: sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances"); break; diff -r f2d3b5c5a68d -r 7e123f047771 MySQL/NEWS --- a/MySQL/NEWS Thu Jun 22 12:15:48 2023 +0200 +++ b/MySQL/NEWS Thu Jun 22 18:07:41 2023 +0200 @@ -1,6 +1,9 @@ Pending changes in the mainline =============================== +* Optimization of LookupResources mainly used in tools/find, C-Find and QIDO-RS. + The optimization mainly affects find at study level. + Release 5.0 (2023-04-16) ======================== diff -r f2d3b5c5a68d -r 7e123f047771 Odbc/NEWS --- a/Odbc/NEWS Thu Jun 22 12:15:48 2023 +0200 +++ b/Odbc/NEWS Thu Jun 22 18:07:41 2023 +0200 @@ -1,6 +1,8 @@ Pending changes in the mainline =============================== +* Optimization of LookupResources mainly used in tools/find, C-Find and QIDO-RS. + The optimization mainly affects find at study level. * Added support for labels * Compatibility with Orthanc SDK 1.12.0 (communications between the Orthanc core and the database plugin using Google Protocol Buffers) diff -r f2d3b5c5a68d -r 7e123f047771 PostgreSQL/NEWS --- a/PostgreSQL/NEWS Thu Jun 22 12:15:48 2023 +0200 +++ b/PostgreSQL/NEWS Thu Jun 22 18:07:41 2023 +0200 @@ -1,6 +1,8 @@ Pending changes in the mainline =============================== +* Optimization of LookupResources mainly used in tools/find, C-Find and QIDO-RS. + The optimization mainly affects find at study level. Release 5.0 (2023-04-15) ======================== diff -r f2d3b5c5a68d -r 7e123f047771 Resources/Orthanc/Databases/ISqlLookupFormatter.cpp --- a/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp Thu Jun 22 12:15:48 2023 +0200 +++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp Thu Jun 22 18:07:41 2023 +0200 @@ -475,23 +475,14 @@ } - - - - void ISqlLookupFormatter::Apply(std::string& sql, - ISqlLookupFormatter& formatter, - const std::vector& lookup, - ResourceType queryLevel, - const std::set& labels, - LabelsConstraint labelsConstraint, - size_t limit) + void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel, ResourceType& upperLevel, const ResourceType& queryLevel, const std::vector& lookup) { assert(ResourceType_Patient < ResourceType_Study && ResourceType_Study < ResourceType_Series && ResourceType_Series < ResourceType_Instance); - ResourceType upperLevel = queryLevel; - ResourceType lowerLevel = queryLevel; + lowerLevel = queryLevel; + upperLevel = queryLevel; for (size_t i = 0; i < lookup.size(); i++) { @@ -507,7 +498,20 @@ lowerLevel = level; } } - + } + + + void ISqlLookupFormatter::Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& labels, + LabelsConstraint labelsConstraint, + size_t limit) + { + ResourceType lowerLevel, upperLevel; + GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); + assert(upperLevel <= queryLevel && queryLevel <= lowerLevel); @@ -608,39 +612,20 @@ } - void ISqlLookupFormatter::ApplyExperimental(std::string& sql, - ISqlLookupFormatter& formatter, - const std::vector& lookup, - ResourceType queryLevel, - const std::set& labels, - LabelsConstraint labelsConstraint, - size_t limit - ) + void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& labels, + LabelsConstraint labelsConstraint, + size_t limit + ) { - assert(ResourceType_Patient < ResourceType_Study && - ResourceType_Study < ResourceType_Series && - ResourceType_Series < ResourceType_Instance); + ResourceType lowerLevel, upperLevel; + GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); - ResourceType upperLevel = queryLevel; - ResourceType lowerLevel = queryLevel; - - for (size_t i = 0; i < lookup.size(); i++) - { - ResourceType level = lookup[i].GetLevel(); - - if (level < upperLevel) - { - upperLevel = level; - } - - if (level > lowerLevel) - { - lowerLevel = level; - } - } - - assert(upperLevel <= queryLevel && - queryLevel <= lowerLevel); + assert(upperLevel == queryLevel && + queryLevel == lowerLevel); const bool escapeBrackets = formatter.IsEscapeBrackets(); @@ -703,25 +688,29 @@ } std::string condition; + std::string inOrNotIn; switch (labelsConstraint) { case LabelsConstraint_Any: condition = "> 0"; + inOrNotIn = "IN"; break; case LabelsConstraint_All: condition = "= " + boost::lexical_cast(labels.size()); + inOrNotIn = "IN"; break; case LabelsConstraint_None: - condition = "= 0"; + condition = "> 0"; + inOrNotIn = "NOT IN"; break; default: throw OrthancException(ErrorCode_ParameterOutOfRange); } - sql += (" AND internalId IN (SELECT id" + sql += (" AND internalId " + inOrNotIn + " (SELECT id" " FROM (SELECT id, COUNT(1) AS labelsCount " "FROM Labels " "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id" diff -r f2d3b5c5a68d -r 7e123f047771 Resources/Orthanc/Databases/ISqlLookupFormatter.h --- a/Resources/Orthanc/Databases/ISqlLookupFormatter.h Thu Jun 22 12:15:48 2023 +0200 +++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.h Thu Jun 22 18:07:41 2023 +0200 @@ -63,6 +63,8 @@ **/ virtual bool IsEscapeBrackets() const = 0; + static void GetLookupLevels(ResourceType& lowerLevel, ResourceType& upperLevel, const ResourceType& queryLevel, const std::vector& lookup); + static void Apply(std::string& sql, ISqlLookupFormatter& formatter, const std::vector& lookup, @@ -71,12 +73,12 @@ LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 size_t limit); - static void ApplyExperimental(std::string& sql, - ISqlLookupFormatter& formatter, - const std::vector& lookup, - ResourceType queryLevel, - const std::set& labels, // New in Orthanc 1.12.0 - LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 - size_t limit); + static void ApplySingleLevel(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& labels, // New in Orthanc 1.12.0 + LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 + size_t limit); }; }