# HG changeset patch # User Alain Mazy # Date 1720163754 -7200 # Node ID c49136b3489134163848a046f61ed25a062e8734 # Parent 2ab3d45c0b3cb730170ed63cd878a650a20d6aec use a prepared statement for InsertOrUpdateMetadata diff -r 2ab3d45c0b3c -r c49136b34891 Framework/Common/DatabasesEnumerations.h --- 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 diff -r 2ab3d45c0b3c -r c49136b34891 Framework/Common/Dictionary.cpp --- 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); diff -r 2ab3d45c0b3c -r c49136b34891 Framework/Common/Dictionary.h --- 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; diff -r 2ab3d45c0b3c -r c49136b34891 Framework/Common/Integer32Value.cpp --- /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 . + **/ + + +#include "Integer32Value.h" + +#include "BinaryStringValue.h" +#include "NullValue.h" +#include "Utf8StringValue.h" + +#include + +#include + +namespace OrthancDatabases +{ + IValue* Integer32Value::Convert(ValueType target) const + { + std::string s = boost::lexical_cast(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); + } + } +} diff -r 2ab3d45c0b3c -r c49136b34891 Framework/Common/Integer32Value.h --- /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 . + **/ + + +#pragma once + +#include "IValue.h" + +#include + +#include + +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; + }; +} diff -r 2ab3d45c0b3c -r c49136b34891 Framework/PostgreSQL/PostgreSQLStatement.cpp --- 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(parameters.GetValue(name)).GetValue()); break; + case ValueType_Integer32: + BindInteger(i, dynamic_cast(parameters.GetValue(name)).GetValue()); + break; + case ValueType_Null: BindNull(i); break; diff -r 2ab3d45c0b3c -r c49136b34891 PostgreSQL/NEWS --- 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) diff -r 2ab3d45c0b3c -r c49136b34891 PostgreSQL/Plugins/PostgreSQLIndex.cpp --- 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(i); - - args.SetUtf8Value(argName, metadata[i].value); + std::string resourceArgName = "r" + boost::lexical_cast(i); + std::string typeArgName = "t" + boost::lexical_cast(i); + std::string valueArgName = "v" + boost::lexical_cast(i); - resourceIds.push_back(boost::lexical_cast(metadata[i].resource)); - metadataTypes.push_back(boost::lexical_cast(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(i), + statement.SetParameterType("v" + boost::lexical_cast(i), ValueType_Utf8String); + statement.SetParameterType("r" + boost::lexical_cast(i), + ValueType_Integer64); + statement.SetParameterType("t" + boost::lexical_cast(i), + ValueType_Integer32); } statement.Execute(args); diff -r 2ab3d45c0b3c -r c49136b34891 Resources/CMake/DatabasesFrameworkConfiguration.cmake --- 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