[PROJ] Performance when Auxilary DB used

[Javier Jimenez Shaw]

Hi Weston.

That looks promising. Thank you.
If you want a "big" auxiliary database (with about extra 1900 CRSs) there
is one in https://github.com/jjimenezshaw/NSRS-2022-PROJ
The file itself is working with PROJ 9.8.1, not with master (there was a
change in the schema, that I will update with 9.9.0).

Best
Javier.

On Mon, 31 Aug 2026 at 18:13, Weston Renoud via PROJ <proj at lists.osgeo.org>
wrote:

> Hi all,
>
> Even, Javier, and I discussed at FOSS4G EU some topics I could explore to
> get more familiar with PROJ and one of the ideas was to explore performance
> when using an auxiliary database. I have poked at the problem and wanted to
> share what I have found so far.
>
> TL&DR I think I may have a found an improvement that reduces the auxillary
> database overhead by about 50%.
>
> The diagnostics I'm using, and the improvement can be found in the branch
> https://github.com/OSGeo/PROJ/compare/master...wrenoud:PROJ:investigate-aux-db-performance
>
> I focused on the usage of an empty auxiliary database with cs2cs, with a
> simple projection of WGS 84 / UTM zone 1N coordinates. In windows command
> prompt this looks like:
>
> set PROJ_AUX_DB=<path-to>/aux.db
> set PROJ_DEBUG=2
> cs2cs EPSG:4236 EPSG:32601 test.txt
>
> I develop primarily on Windows and Linux for Intel CPUs, so I make use of
> Intel's Vtune Profiler when I'm looking to evaluate code performance. I
> instrumented the method `DatabaseContext::Private::run` with begin and end
> gates to time the SQL queries to try to identify if there are particular
> queries that perform especially poorly. What I found:
>
> Usage of `DatabaseContext::Private::run`:
> without auxiliary database: 43 SQL queries, totaling ~3ms.
> with empty auxiliary database: 221 SQL queries, totaling ~70ms.
>
> With the auxiliary database the extra queries and time could be broken
> down into 3 main categories
> * Setup - about 20ms (97 queries) of the 70ms when using the auxiliary
> database is related to initial setup
> * Duplicates - for queries that don't need cross referencing, they are
> called directly on each attached database and have comparable performance.
> * Usage query - about 40-50ms (11 queries)
>
> The setup does three main things: ATTACHING each database, querying the
> table structures, and creating temporary VIEWs to UNION the tables between
> the attached databases. These are required to support cross referencing
> from the auxiliary database(s) to items in the primary database, and it is
> not obvious there is any room for improvements.
>
> The duplicates on each attached database are necessary given the
> additional/auxillary database. Again, it is not obvious there is any room
> for improvements here.
>
> The usage query: of the queries on the UNION'ed VIEWs, the query from
> `AuthorityFactory::Private::createPropertiesSearchUsages` jumped out given
> the significant run time. Without an auxiliary database these queries sum
> to less than 0.1ms, but with an empty auxiliary database they sum to
> 40-50ms. This query requires a join between the usage, extent and scope
> tables.
>
> SELECT extent.description,
>   extent.south_lat,
>   extent.north_lat,
>   extent.west_lon,
>   extent.east_lon,
>   scope.scope,
>   (CASE WHEN scope.scope LIKE '%large scale%' THEN 0 ELSE 1 END) AS score
> FROM usage
> JOIN extent ON usage.extent_auth_name = extent.auth_name AND
> usage.extent_code = extent.code
> JOIN scope ON usage.scope_auth_name = scope.auth_name AND usage.scope_code
> = scope.code
> WHERE object_table_name = ?
>  AND object_auth_name = ?
>  AND object_code = ?
>  AND NOT (usage.extent_auth_name = 'PROJ' AND usage.extent_code =
> 'EXTENT_UNKNOWN')
>  AND NOT (usage.scope_auth_name = 'PROJ' AND usage.scope_code =
> 'SCOPE_UNKNOWN')
> ORDER BY score, usage.auth_name, usage.code
>
> Trying to optimize this query seemed like a good target, so for the next
> step I tried to learn more about it by making use of `EXPLAIN QUERY PLAN` (
> https://sqlite.org/eqp.html).
>
> Without the auxiliary database, the query execution is very
> straightforward and fast. It is executed as three searches by index or
> primary key:
>
> 6,0,43,SEARCH usage USING INDEX idx_usage_object (object_table_name=? AND
> object_auth_name=? AND object_code=?)
> 25,0,39,SEARCH extent USING PRIMARY KEY (auth_name=? AND code=?)
> 31,0,34,SEARCH scope USING PRIMARY KEY (auth_name=? AND code=?)
> 58,0,0,USE TEMP B-TREE FOR ORDER BY
>
> With the auxiliary database the query plan bloats to this due to the JOINs
> over the UNION'ed tables:
>
> 2,0,0,CO-ROUTINE usage
> 3,2,0,COMPOUND QUERY
> 4,3,0,LEFT-MOST SUBQUERY
> 7,4,43,SEARCH db_0.usage USING INDEX idx_usage_object (object_table_name=?
> AND object_auth_name=? AND object_code=?)
> 37,3,0,UNION ALL
> 39,37,216,SCAN db_1.usage
> 69,0,0,MATERIALIZE extent
> 71,69,0,COMPOUND QUERY
> 72,71,0,LEFT-MOST SUBQUERY
> 74,72,135,SCAN db_0.extent
> 93,71,0,UNION ALL
> 95,93,215,SCAN db_1.extent
> 117,0,0,MATERIALIZE scope
> 119,117,0,COMPOUND QUERY
> 120,119,0,LEFT-MOST SUBQUERY
> 122,120,97,SCAN db_0.scope
> 132,119,0,UNION ALL
> 134,132,215,SCAN db_1.scope
> 146,0,196,SCAN usage
> 167,0,0,BLOOM FILTER ON extent (code=? AND auth_name=?)
> 188,0,53,SEARCH extent USING AUTOMATIC COVERING INDEX (code=? AND
> auth_name=?)
> 201,0,0,BLOOM FILTER ON scope (code=? AND auth_name=?)
> 213,0,53,SEARCH scope USING AUTOMATIC COVERING INDEX (code=? AND
> auth_name=?)
> 245,0,0,USE TEMP B-TREE FOR ORDER BY
>
> Of note are SCAN and MATERIALIZE. When combined, they effectively
> duplicate the table's contents into temporary tables for the query.
>
> Based on this I then explored if creating tables, instead of views for the
> extent and usage tables would improve the performance. This dropped the
> total query time (for all 11 calls) to ~3ms, with an overhead of ~4ms to
> create the tables. That is over 30ms reduction. But again, this is with an
> empty auxiliary database.
>
> I will test further by putting some entries into the auxiliary tables and
> see if there is a significant difference. I'm also curious if anyone with
> more sqlite experience might have some ideas based on these findings? Or if
> there are other concerns or considerations I should be thinking about here?
>
> Best Regards,
> Weston
>
> _______________________________________________
> PROJ mailing list
> PROJ at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/proj
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/proj/attachments/20260831/4ce91808/attachment.htm>