Perfomance degradation with auxiliary database

[AlexandrePTJ]

When using an auxiliary database, database queries are about 20 times slower than usual.

Here a small code to reproduce this.

#include <array>
#include <chrono>
#include <filesystem>
#include <iostream>
#include <stdexcept>
#include <string>

#include <proj.h>
#include <sqlite3.h>

void listEpsgCRS(PJ_CONTEXT *ctx)
{
  auto listParameters = proj_get_crs_list_parameters_create();
  auto crsInfos       = proj_get_crs_info_list_from_database(ctx, "EPSG", listParameters, nullptr);

  size_t nCrsInfos = 0;
  for (auto crsInfoIt = crsInfos; crsInfoIt != nullptr && *crsInfoIt != nullptr; ++crsInfoIt)
  {
    ++nCrsInfos;
  }
  std::cout << "Found " << std::to_string(nCrsInfos) << " CRS" << std::endl;
  proj_crs_info_list_destroy(crsInfos);
}

void createAuxDb(PJ_CONTEXT *ctx, const char *dbPath)
{
  if (std::filesystem::exists(dbPath))
  {
    std::filesystem::remove(dbPath);
  }

  sqlite3 *db = nullptr;
  int rc = sqlite3_open(dbPath, &db);
  if (rc != SQLITE_OK)
  {
    throw std::runtime_error("Cannot create aux db");
  }

  auto sqlStmts = proj_context_get_database_structure(ctx, nullptr);
  for (auto sqlStmtIt = sqlStmts; sqlStmtIt != nullptr && *sqlStmtIt != nullptr; ++sqlStmtIt)
  {
    rc = sqlite3_exec(db, *sqlStmtIt, nullptr, nullptr, nullptr);
    if (rc != SQLITE_OK)
    {
      sqlite3_close(db);
      throw std::runtime_error(std::string("Error on aux db creation: ") + sqlite3_errmsg(db));
    }
  }
  sqlite3_close(db);
}

int main(int /*argc*/, char * /*argv*/[])
{
  try
  {
    // 1. Setup context with proj.db only
    auto ctx = proj_context_create();

    // 2. List available CRS for epsg
    auto tpStart = std::chrono::high_resolution_clock::now();
    listEpsgCRS(ctx);
    auto tpDelta = std::chrono::duration_cast<std::chrono::milliseconds>(std::chrono::high_resolution_clock::now() - tpStart).count();
    std::cout << "Duration for listing EPSG CRS without aux db: " << std::to_string(tpDelta) << " ms" << std::endl;

    // 3. Create aux database
    const auto auxDbPath = "./proj_aux.db";
    createAuxDb(ctx, auxDbPath);

    // 4. Add aux database to context
    // WARNING: Error in proj doc: it says it require a string with ':' or ';' separators. However, implementation requires an array with nullptr for last element.
    std::array<const char *, 2> auxDbPaths = {auxDbPath, nullptr};
    proj_context_set_database_path(ctx, nullptr, auxDbPaths.data(), nullptr);

    // 5. List available CRS for epsg
    tpStart = std::chrono::high_resolution_clock::now();
    listEpsgCRS(ctx);
    tpDelta = std::chrono::duration_cast<std::chrono::milliseconds>(std::chrono::high_resolution_clock::now() - tpStart).count();
    std::cout << "Duration for listing EPSG CRS with aux db: " << std::to_string(tpDelta) << " ms" << std::endl;

    proj_context_destroy(ctx);
  }
  catch (std::runtime_error &ex)
  {
      std::cerr << ex.what() << std::endl;
  }
  return 0;
}

On debian, the output gives:

Found 6340 CRS
Duration for listing EPSG CRS without aux db: 46 ms
Found 6340 CRS
Duration for listing EPSG CRS with aux db: 894 ms

Problem description

This delay is only for one authority, but in my application I request all authorities. So it tooks much more time.

Expected Output

I suppose it can be 2 or 3 times longer.

Environment Information

• PROJ version 8.1.0
• Debian 11
• Proj obtained from conan