[PROJ] Performance when Auxilary DB used

Mon Aug 31 09:43:36 PDT 2026

Hi Weston,

thanks for this interesting analysis. I'm wondering if removing the 
ORDER BY clause would have a significant effect on the performance, if 
ordering would be the reason for materializing ? Sorting could be easily 
done on the C++ side. And the expected number of rows of the query in in 
99% of the cases is just 1, and the remaining 1% 2, but SQLite has of 
course no clue about that.

Otherwise you could try to submit the question to the SQLite experts at 
https://sqlite.org/forum/forum, but to have some hope of them having a 
look at that, you should provide an easy reproducer using the sqlite3 
binary and provide the databases likely stripped down to only contain 
the needed tables to facilitate their analysis.

Cheers,

Even

Le 31/08/2026 à 18:13, Weston Renoud via PROJ a écrit :
> 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 
> <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 <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

-- 
http://www.spatialys.com
My software is free, but my time generally not.
LLMs contribute to global warming and brain rot.
Let's guillotine them! "Ah ! ça ira, ça ira, ça ira !"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/proj/attachments/20260831/2d896822/attachment-0001.htm>