Mercurial > hg > orthanc
changeset 7150:d0627441796a default tip
SQLite: run ANALYZE or OPTIMIZE to improve query planner
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Wed, 16 Sep 2026 14:55:01 +0200 |
| parents | 5c5392a01134 |
| children | |
| files | NEWS OrthancFramework/Sources/SQLite/Connection.cpp OrthancFramework/Sources/SQLite/Connection.h OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp |
| diffstat | 4 files changed, 52 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/NEWS Wed Sep 16 14:54:38 2026 +0200 +++ b/NEWS Wed Sep 16 14:55:01 2026 +0200 @@ -1,6 +1,14 @@ Pending changes in the mainline =============================== +General +------- + +* With the default SQLite database: + - Optimized some queries to access single resources. + - Optimized many queries thanks to improved query planning. An Optimization step can + sometimes run at startup which slows down the Orthanc startup. + Plugin SDK ----------
--- a/OrthancFramework/Sources/SQLite/Connection.cpp Wed Sep 16 14:54:38 2026 +0200 +++ b/OrthancFramework/Sources/SQLite/Connection.cpp Wed Sep 16 14:55:01 2026 +0200 @@ -232,6 +232,31 @@ return false; } + void Connection::Optimize(bool onlyIfStatsDontExists) + { + if (onlyIfStatsDontExists) + { + if (!DoesTableExist("sqlite_stat1")) // create the table if it does not exist yet and if there are enough data in the Resources table + { + Statement countResources(*this, "SELECT COUNT(*) FROM Resources"); + countResources.Step(); + if (countResources.ColumnInt64(0) > 1000) // no need to ANALYZE if there are not enough data in the table + { + CLOG(WARNING, SQLITE) << "SQLite: Performing first ANALYZE to improve the query planner"; + Statement analyze(*this, "ANALYZE"); + analyze.Run(); + } + } + } + else + { + CLOG(WARNING, SQLITE) << "SQLite: Performing OPTIMIZE to improve the query planner"; + Statement analyze(*this, "PRAGMA OPTIMIZE"); // OPTIMIZE runs ANALYZE only if it makes sense + analyze.Run(); + CLOG(WARNING, SQLITE) << "SQLite: OPTIMIZE completed"; + } + } + int64_t Connection::GetLastInsertRowId() const { return sqlite3_last_insert_rowid(db_);
--- a/OrthancFramework/Sources/SQLite/Connection.h Wed Sep 16 14:54:38 2026 +0200 +++ b/OrthancFramework/Sources/SQLite/Connection.h Wed Sep 16 14:55:01 2026 +0200 @@ -121,6 +121,8 @@ void FlushToDisk(); + void Optimize(bool onlyIfStatsDontExists); + IScalarFunction* Register(IScalarFunction* func); // Takes the ownership of the function // Info querying -------------------------------------------------------------
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Wed Sep 16 14:54:38 2026 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Wed Sep 16 14:55:01 2026 +0200 @@ -1094,6 +1094,16 @@ 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 ! + // uncomment to show the execution plan (in dev mode only of course !) + // { + // SQLite::Statement analyze(db_, SQLITE_FROM_HERE_DYNAMIC(std::string("EXPLAIN QUERY PLAN ") + sql), std::string("EXPLAIN QUERY PLAN ") + sql); + // CLOG(TRACE, SQLITE) << "EXPLAIN QUERY PLAN for " << sql; + // while (analyze.Step()) + // { + // CLOG(TRACE, SQLITE) << "id: " << analyze.ColumnString(0) << " parent: " << analyze.ColumnString(1) << " op: " << analyze.ColumnString(3); + // } + // } + SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql); formatter.Bind(s); @@ -2873,6 +2883,9 @@ LOG(INFO) << "Adding timeout column to the \"Queues\" table"; ExecuteEmbeddedScript(db_, ServerResources::ADD_TIMEOUT_TO_QUEUES); } + + // New in Orthanc 1.13.1 run optimize at each startup + db_.Optimize(false); } transaction->Commit(0); @@ -3010,6 +3023,10 @@ void SQLiteDatabaseWrapper::FlushToDisk() { boost::recursive_mutex::scoped_lock lock(mutex_); + + // this will run ANALYZE only once whe the Resources table contains enough rows + db_.Optimize(true); + db_.FlushToDisk(); }