Mercurial > hg > orthanc-databases

changeset 522:c49136b34891 large-queries tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
use a prepared statement for InsertOrUpdateMetadata
author Alain Mazy <am@orthanc.team>
date Fri, 05 Jul 2024 09:15:54 +0200
parents 2ab3d45c0b3c
children
files Framework/Common/DatabasesEnumerations.h Framework/Common/Dictionary.cpp Framework/Common/Dictionary.h Framework/Common/Integer32Value.cpp Framework/Common/Integer32Value.h Framework/PostgreSQL/PostgreSQLStatement.cpp PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp Resources/CMake/DatabasesFrameworkConfiguration.cmake
diffstat 9 files changed, 155 insertions(+), 10 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Common/DatabasesEnumerations.h	Thu Jul 04 07:44:39 2024 +0200
+++ b/Framework/Common/DatabasesEnumerations.h	Fri Jul 05 09:15:54 2024 +0200
@@ -31,6 +31,7 @@
     ValueType_BinaryString,
     ValueType_InputFile,
     ValueType_Integer64,
+    ValueType_Integer32,
     ValueType_Null,
     ValueType_ResultFile,
     ValueType_Utf8String
--- a/Framework/Common/Dictionary.cpp	Thu Jul 04 07:44:39 2024 +0200
+++ b/Framework/Common/Dictionary.cpp	Fri Jul 05 09:15:54 2024 +0200
@@ -25,6 +25,7 @@
 
 #include "BinaryStringValue.h"
 #include "InputFileValue.h"
+#include "Integer32Value.h"
 #include "Integer64Value.h"
 #include "NullValue.h"
 #include "Utf8StringValue.h"
@@ -126,7 +127,13 @@
     SetValue(key, new Integer64Value(value));
   }
 
-  
+
+  void Dictionary::SetInteger32Value(const std::string& key,
+                                     int32_t value)
+  {
+    SetValue(key, new Integer32Value(value));
+  }
+
   void Dictionary::SetNullValue(const std::string& key)
   {
     SetValue(key, new NullValue);
--- a/Framework/Common/Dictionary.h	Thu Jul 04 07:44:39 2024 +0200
+++ b/Framework/Common/Dictionary.h	Fri Jul 05 09:15:54 2024 +0200
@@ -68,6 +68,9 @@
     void SetIntegerValue(const std::string& key,
                          int64_t value);
 
+    void SetInteger32Value(const std::string& key,
+                           int32_t value);
+
     void SetNullValue(const std::string& key);
 
     const IValue& GetValue(const std::string& key) const;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/Integer32Value.cpp	Fri Jul 05 09:15:54 2024 +0200
@@ -0,0 +1,55 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#include "Integer32Value.h"
+
+#include "BinaryStringValue.h"
+#include "NullValue.h"
+#include "Utf8StringValue.h"
+
+#include <OrthancException.h>
+
+#include <boost/lexical_cast.hpp>
+
+namespace OrthancDatabases
+{
+  IValue* Integer32Value::Convert(ValueType target) const
+  {
+    std::string s = boost::lexical_cast<std::string>(value_);
+            
+    switch (target)
+    {
+      case ValueType_Null:
+        return new NullValue;
+
+      case ValueType_BinaryString:
+        return new BinaryStringValue(s);
+
+      case ValueType_Utf8String:
+        return new Utf8StringValue(s);
+
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+    }
+  }
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/Integer32Value.h	Fri Jul 05 09:15:54 2024 +0200
@@ -0,0 +1,57 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#pragma once
+
+#include "IValue.h"
+
+#include <Compatibility.h>
+
+#include <stdint.h>
+
+namespace OrthancDatabases
+{
+  class Integer32Value : public IValue
+  {
+  private:
+    int32_t  value_;
+
+  public:
+    explicit Integer32Value(int32_t value) :
+    value_(value)
+    {
+    }
+
+    int32_t GetValue() const
+    {
+      return value_;
+    }
+
+    virtual ValueType GetType() const ORTHANC_OVERRIDE
+    {
+      return ValueType_Integer32;
+    }
+    
+    virtual IValue* Convert(ValueType target) const ORTHANC_OVERRIDE;
+  };
+}
--- a/Framework/PostgreSQL/PostgreSQLStatement.cpp	Thu Jul 04 07:44:39 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp	Fri Jul 05 09:15:54 2024 +0200
@@ -26,6 +26,7 @@
 
 #include "../Common/BinaryStringValue.h"
 #include "../Common/InputFileValue.h"
+#include "../Common/Integer32Value.h"
 #include "../Common/Integer64Value.h"
 #include "../Common/NullValue.h"
 #include "../Common/ResultBase.h"
@@ -338,6 +339,10 @@
           DeclareInputInteger64(i);
           break;
 
+        case ValueType_Integer32:
+          DeclareInputInteger(i);
+          break;
+
         case ValueType_Utf8String:
           DeclareInputString(i);
           break;
@@ -529,6 +534,10 @@
           BindInteger64(i, dynamic_cast<const Integer64Value&>(parameters.GetValue(name)).GetValue());
           break;
 
+        case ValueType_Integer32:
+          BindInteger(i, dynamic_cast<const Integer32Value&>(parameters.GetValue(name)).GetValue());
+          break;
+
         case ValueType_Null:
           BindNull(i);
           break;
--- a/PostgreSQL/NEWS	Thu Jul 04 07:44:39 2024 +0200
+++ b/PostgreSQL/NEWS	Fri Jul 05 09:15:54 2024 +0200
@@ -8,7 +8,11 @@
 * Fix updates from plugin version 3.3 to latest version
 * Added support for ExtendedApiV1:
   - changes?type=...&to=...
-
+* Performance optimizations (to be summarized before release):
+  - using more prepared SQL statements:
+    - InsertOrUpdateMetadata
+  - 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)
 
 
 Release 6.2 (2024-03-25)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Thu Jul 04 07:44:39 2024 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Jul 05 09:15:54 2024 +0200
@@ -552,13 +552,17 @@
     
     for (uint32_t i = 0; i < count; i++)
     {
-      std::string argName = "m" + boost::lexical_cast<std::string>(i);
-
-      args.SetUtf8Value(argName, metadata[i].value);
+      std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i);
+      std::string typeArgName = "t" + boost::lexical_cast<std::string>(i);
+      std::string valueArgName = "v" + boost::lexical_cast<std::string>(i);
 
-      resourceIds.push_back(boost::lexical_cast<std::string>(metadata[i].resource));
-      metadataTypes.push_back(boost::lexical_cast<std::string>(metadata[i].metadata));
-      metadataValues.push_back("${" + argName + "}");
+      args.SetIntegerValue(resourceArgName, metadata[i].resource);
+      args.SetInteger32Value(typeArgName, metadata[i].metadata);
+      args.SetUtf8Value(valueArgName, metadata[i].value);
+
+      resourceIds.push_back("${" + resourceArgName + "}");
+      metadataTypes.push_back("${" + typeArgName + "}");
+      metadataValues.push_back("${" + valueArgName + "}");
       revisions.push_back("0");
     }
 
@@ -578,12 +582,16 @@
                                   joinedMetadataValues + "], ARRAY[" + 
                                   joinedRevisions + "])";
 
-    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("m" + boost::lexical_cast<std::string>(i),
+      statement.SetParameterType("v" + boost::lexical_cast<std::string>(i),
                                   ValueType_Utf8String);
+      statement.SetParameterType("r" + boost::lexical_cast<std::string>(i),
+                                  ValueType_Integer64);
+      statement.SetParameterType("t" + boost::lexical_cast<std::string>(i),
+                                  ValueType_Integer32);
     }
 
     statement.Execute(args);
--- a/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Thu Jul 04 07:44:39 2024 +0200
+++ b/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Fri Jul 05 09:15:54 2024 +0200
@@ -113,6 +113,7 @@
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/IResult.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/ImplicitTransaction.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/InputFileValue.cpp
+  ${ORTHANC_DATABASES_ROOT}/Framework/Common/Integer32Value.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/Integer64Value.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/NullValue.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/Query.cpp