Mercurial > hg > orthanc-databases

changeset 523:9413451fd984 large-queries

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
ExecuteSetResourcesContentTags is now using a cached prepared statement
author Alain Mazy <am@orthanc.team>
date Tue, 09 Jul 2024 16:21:25 +0200
parents c49136b34891
children 48aba35fe64e
files PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp
diffstat 2 files changed, 32 insertions(+), 16 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/NEWS	Fri Jul 05 09:15:54 2024 +0200
+++ b/PostgreSQL/NEWS	Tue Jul 09 16:21:25 2024 +0200
@@ -11,8 +11,9 @@
 * Performance optimizations (to be summarized before release):
   - using more prepared SQL statements:
     - InsertOrUpdateMetadata
+    - ExecuteSetResourcesContentTags
   - reduced the number of round-trips between Orthanc and the PostgreSQL server:
-    - e.g: when receiving an instance in an existing series, reduced the number of SQL queries from 13 to 12 (to be continued)
+    - e.g: when receiving an instance in an existing series, reduced the number of SQL queries from 13 to 10 (to be continued)
 
 
 Release 6.2 (2024-03-25)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Jul 05 09:15:54 2024 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Tue Jul 09 16:21:25 2024 +0200
@@ -487,23 +487,34 @@
   static void ExecuteSetResourcesContentTags(
     DatabaseManager& manager,
     const std::string& table,
-    const std::string& variablePrefix,
     uint32_t count,
     const OrthancPluginResourcesContentTags* tags)
   {
     std::string sql;
+
+    std::vector<std::string> resourceIds;
+    std::vector<std::string> groups;
+    std::vector<std::string> elements;
+    std::vector<std::string> values;
+
     Dictionary args;
     
     for (uint32_t i = 0; i < count; i++)
     {
-      std::string name = variablePrefix + boost::lexical_cast<std::string>(i);
+      std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i);
+      std::string groupArgName = "g" + boost::lexical_cast<std::string>(i);
+      std::string elementArgName = "e" + boost::lexical_cast<std::string>(i);
+      std::string valueArgName = "v" + boost::lexical_cast<std::string>(i);
 
-      args.SetUtf8Value(name, tags[i].value);
-      
-      std::string insert = ("(" + boost::lexical_cast<std::string>(tags[i].resource) + ", " +
-                            boost::lexical_cast<std::string>(tags[i].group) + ", " +
-                            boost::lexical_cast<std::string>(tags[i].element) + ", " +
-                            "${" + name + "})");
+      args.SetIntegerValue(resourceArgName, tags[i].resource);
+      args.SetInteger32Value(elementArgName, tags[i].element);
+      args.SetInteger32Value(groupArgName, tags[i].group);
+      args.SetUtf8Value(valueArgName, tags[i].value);
+
+      std::string insert = ("(${" + resourceArgName + "}, ${" +
+                            groupArgName + "}, ${" +
+                            elementArgName + "}, " +
+                            "${" + valueArgName + "})");
 
       if (sql.empty())
       {
@@ -517,11 +528,17 @@
 
     if (!sql.empty())
     {
-      DatabaseManager::StandaloneStatement statement(manager, sql);
-
+      DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
+      
       for (uint32_t i = 0; i < count; i++)
       {
-        statement.SetParameterType(variablePrefix + boost::lexical_cast<std::string>(i),
+        statement.SetParameterType("r" + boost::lexical_cast<std::string>(i),
+                                    ValueType_Integer64);
+        statement.SetParameterType("g" + boost::lexical_cast<std::string>(i),
+                                    ValueType_Integer32);
+        statement.SetParameterType("e" + boost::lexical_cast<std::string>(i),
+                                    ValueType_Integer32);
+        statement.SetParameterType("v" + boost::lexical_cast<std::string>(i),
                                    ValueType_Utf8String);
       }
 
@@ -607,11 +624,9 @@
                                      uint32_t countMetadata,
                                      const OrthancPluginResourcesContentMetadata* metadata)
   {
-    ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i",
-                                   countIdentifierTags, identifierTags);
+    ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", countIdentifierTags, identifierTags);
 
-    ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t",
-                                   countMainDicomTags, mainDicomTags);
+    ExecuteSetResourcesContentTags(manager, "MainDicomTags", countMainDicomTags, mainDicomTags);
     
     ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata);