<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Hey Even,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt;">
<span style="color: rgb(0, 0, 0);">Removing the ORDER BY clause removed the need to materialize and gives the same query performance as with the temporary tables, but without the need to create any temporary tables which took extra time. Adding back just the
"</span><span style="color: black;">ORDER BY </span><span style="color: rgb(0, 0, 0);">score" still avoided the materialize, it seemed to be the "</span><span style="color: black;">usage.auth_name, usage.code" that triggered the materialize. Are those necessary,
or is "ORDER BY score" sufficient?</span></div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Cheers,</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
Weston</div>
<div class="elementToProof" style="font-family: Aptos, Aptos_EmbeddedFont, Aptos_MSFontService, Calibri, Helvetica, sans-serif; font-size: 12pt; color: rgb(0, 0, 0);">
<br>
</div>
<div id="appendonsend"></div>
<hr style="display:inline-block;width:98%" tabindex="-1">
<div id="divRplyFwdMsg" dir="ltr"><font face="Calibri, sans-serif" style="font-size:11pt" color="#000000"><b>From:</b> Even Rouault <even.rouault@spatialys.com><br>
<b>Sent:</b> Monday, August 31, 2026 6:43 PM<br>
<b>To:</b> Weston Renoud <weston.renoud@qps.nl>; proj@lists.osgeo.org <proj@lists.osgeo.org><br>
<b>Subject:</b> Re: [PROJ] Performance when Auxilary DB used</font>
<div> </div>
</div>
<div>
<table border="0" cellspacing="0" cellpadding="0" width="100%" align="left" style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important; border:0!important; display:table!important; width:100%!important; table-layout:fixed!important; border-collapse:seperate!important; float:none!important; border-spacing:0px 0px!important">
<tbody style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important; display:block!important">
<tr style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important">
<td valign="middle" width="1px" bgcolor="#A6A6A6" cellpadding="7px 2px 7px 2px" style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important; padding:7px 2px 7px 2px!important; background-color:#A6A6A6!important; width:0px!important">
</td>
<td valign="middle" width="100%" bgcolor="#EAEAEA" cellpadding="7px 5px 7px 15px" color="#212121" style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important; width:100%!important; background-color:#EAEAEA!important; padding:7px 5px 7px 15px!important; font-family:wf_segoe-ui_normal,Segoe UI,Segoe WP,Tahoma,Arial,sans-serif!important; font-size:12px!important; font-weight:normal!important; color:#212121!important; text-align:left!important; word-wrap:break-word!important">
<div style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important">
You don't often get email from even.rouault@spatialys.com. <a href="https://aka.ms/LearnAboutSenderIdentification" style="background:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; opacity:revert!important; visibility:revert!important">
Learn why this is important</a> </div>
</td>
<td valign="middle" align="left" width="75px" bgcolor="#EAEAEA" cellpadding="7px 5px 7px 5px" color="#212121" style="background:revert!important; border:revert!important; bottom:revert!important; color:revert!important; direction:revert!important; display:revert!important; font-size:revert!important; height:revert!important; letter-spacing:revert!important; line-height:revert!important; margin:revert!important; opacity:revert!important; order:revert!important; outline:revert!important; overflow:revert!important; padding:revert!important; position:revert!important; tab-size:revert!important; table-layout:revert!important; text-align:revert!important; text-indent:revert!important; text-orientation:revert!important; text-overflow:revert!important; text-transform:revert!important; top:revert!important; vertical-align:revert!important; visibility:revert!important; white-space:revert!important; width:revert!important; word-break:revert!important; word-spacing:revert!important; writing-mode:revert!important; zoom:revert!important; width:75px!important; background-color:#EAEAEA!important; padding:7px 5px 7px 5px!important; font-family:wf_segoe-ui_normal,Segoe UI,Segoe WP,Tahoma,Arial,sans-serif!important; font-size:12px!important; font-weight:normal!important; color:#212121!important; text-align:left!important; word-wrap:break-word!important">
</td>
</tr>
</tbody>
</table>
<div>
<p>Hi Weston,</p>
<p>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.</p>
<p>Otherwise you could try to submit the question to the SQLite experts at <a class="x_moz-txt-link-freetext" href="https://sqlite.org/forum/forum">
https://sqlite.org/forum/forum</a>, 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.</p>
<p>Cheers,</p>
<p>Even</p>
<div class="x_moz-cite-prefix">Le 31/08/2026 à 18:13, Weston Renoud via PROJ a écrit :<br>
</div>
<blockquote type="cite"><style type="text/css" style="display:none">
<!--
-->
</style>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Hi all,</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
TL&DR I think I may have a found an improvement that reduces the auxillary database overhead by about 50%.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
The diagnostics I'm using, and the improvement can be found in the branch <a href="https://github.com/OSGeo/PROJ/compare/master...wrenoud:PROJ:investigate-aux-db-performance" id="OWAe3197447-9ce6-a41b-13a6-cb74a7311054" class="x_OWAAutoLink x_moz-txt-link-freetext">
https://github.com/OSGeo/PROJ/compare/master...wrenoud:PROJ:investigate-aux-db-performance</a></div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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:</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
set PROJ_AUX_DB=<path-to>/aux.db</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
set PROJ_DEBUG=2</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
cs2cs <a class="x_moz-txt-link-freetext" href="">EPSG:4236</a> <a class="x_moz-txt-link-freetext" href="">
EPSG:32601</a> test.txt</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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 `<a class="x_moz-txt-link-freetext" href="">DatabaseContext::Private::run</a>` 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:</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Usage of `<a class="x_moz-txt-link-freetext" href="">DatabaseContext::Private::run</a>`:</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
without auxiliary database: 43 SQL queries, totaling ~3ms.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
with empty auxiliary database: 221 SQL queries, totaling ~70ms.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
With the auxiliary database the extra queries and time could be broken down into 3 main categories</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
* Setup - about 20ms (97 queries) of the 70ms when using the auxiliary database is related to initial setup</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
* Duplicates - for queries that don't need cross referencing, they are called directly on each attached database and have comparable performance.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
* Usage query - about 40-50ms (11 queries)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
The usage query: of the queries on the UNION'ed VIEWs, the query from `<a class="x_moz-txt-link-freetext" href="">AuthorityFactory::Private::createPropertiesSearchUsages</a>` 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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
SELECT extent.description,<br>
extent.south_lat,<br>
extent.north_lat,<br>
extent.west_lon,<br>
extent.east_lon,<br>
scope.scope,<br>
(CASE WHEN scope.scope LIKE '%large scale%' THEN 0 ELSE 1 END) AS score<br>
FROM usage<br>
JOIN extent ON usage.extent_auth_name = extent.auth_name AND usage.extent_code = extent.code<br>
JOIN scope ON usage.scope_auth_name = scope.auth_name AND usage.scope_code = scope.code<br>
WHERE object_table_name = ?<br>
AND object_auth_name = ?<br>
AND object_code = ?<br>
AND NOT (usage.extent_auth_name = 'PROJ' AND usage.extent_code = 'EXTENT_UNKNOWN')<br>
AND NOT (usage.scope_auth_name = 'PROJ' AND usage.scope_code = 'SCOPE_UNKNOWN')<br>
ORDER BY score, usage.auth_name, usage.code</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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` (<a href="https://sqlite.org/eqp.html" id="OWAc87ad138-e313-4ca5-c0ef-ed1f4d178591" class="x_OWAAutoLink x_moz-txt-link-freetext">https://sqlite.org/eqp.html</a>).</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Without the auxiliary database, the query execution is very straightforward and fast. It is executed as three searches by index or primary key:</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
6,0,43,SEARCH usage USING INDEX idx_usage_object (object_table_name=? AND object_auth_name=? AND object_code=?)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
25,0,39,SEARCH extent USING PRIMARY KEY (auth_name=? AND code=?)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
31,0,34,SEARCH scope USING PRIMARY KEY (auth_name=? AND code=?)</div>
<div style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
58,0,0,USE TEMP B-TREE FOR ORDER BY</div>
<div style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
With the auxiliary database the query plan bloats to this due to the JOINs over the UNION'ed tables:</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
2,0,0,CO-ROUTINE usage</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
3,2,0,COMPOUND QUERY</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
4,3,0,LEFT-MOST SUBQUERY</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
7,4,43,SEARCH db_0.usage USING INDEX idx_usage_object (object_table_name=? AND object_auth_name=? AND object_code=?)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
37,3,0,UNION ALL</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
39,37,216,SCAN db_1.usage</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
69,0,0,MATERIALIZE extent</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
71,69,0,COMPOUND QUERY</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
72,71,0,LEFT-MOST SUBQUERY</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
74,72,135,SCAN db_0.extent</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
93,71,0,UNION ALL</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
95,93,215,SCAN db_1.extent</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
117,0,0,MATERIALIZE scope</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
119,117,0,COMPOUND QUERY</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
120,119,0,LEFT-MOST SUBQUERY</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
122,120,97,SCAN db_0.scope</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
132,119,0,UNION ALL</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
134,132,215,SCAN db_1.scope</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
146,0,196,SCAN usage</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
167,0,0,BLOOM FILTER ON extent (code=? AND auth_name=?)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
188,0,53,SEARCH extent USING AUTOMATIC COVERING INDEX (code=? AND auth_name=?)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
201,0,0,BLOOM FILTER ON scope (code=? AND auth_name=?)</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
213,0,53,SEARCH scope USING AUTOMATIC COVERING INDEX (code=? AND auth_name=?)</div>
<div style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
245,0,0,USE TEMP B-TREE FOR ORDER BY</div>
<div style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Of note are SCAN and MATERIALIZE. When combined, they effectively duplicate the table's contents into temporary tables for the query.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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.</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
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?</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Best Regards,</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
Weston</div>
<div class="x_elementToProof" style="font-family:Aptos,Aptos_EmbeddedFont,Aptos_MSFontService,Calibri,Helvetica,sans-serif; font-size:12pt; color:rgb(0,0,0)">
<br>
</div>
<br>
<fieldset class="x_moz-mime-attachment-header"></fieldset>
<pre class="x_moz-quote-pre">_______________________________________________
PROJ mailing list
<a class="x_moz-txt-link-abbreviated" href="mailto:PROJ@lists.osgeo.org">PROJ@lists.osgeo.org</a>
<a class="x_moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/proj">https://lists.osgeo.org/mailman/listinfo/proj</a>
</pre>
</blockquote>
<pre class="x_moz-signature" cols="72">--
<a class="x_moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a>
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 !"</pre>
</div>
</div>
</body>
</html>