<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /></head><body style='font-size: 10pt; font-family: Verdana,Geneva,sans-serif'>
<p>Hi Even,</p>
<p>Thank you for sharing this finding!</p>
<p>In that case I think that QGIS should offer a flag to open Geopackages in read-only mode.</p>
<p>Even - since you are "Mr. Geopackage" in the QGIS project anyway - can you please make sure that this can be covered in the upcoming Geopackage improvements? Would a "read-only" mode also be useful for other data formats? I opened a ticket at <a href="http://hub.qgis.org/issues/15652">http://hub.qgis.org/issues/15652</a> and assigned it to you. I have no idea how much effort this enhancement would mean - can you please do an estimate and send me a quote for that? Maybe we could still include it in the upcoming Geopackage improvements financed by the Swiss QGIS user group.</p>
<p>-------------------</p>
<p>This also leads to a follow up question: many OGR data formats have "open options". As far as I know, these options are not exposed to the User in QGIS, other than the encoding - or did I miss something? Should we introduce a "GUI" way in QGIS 3.x to expose these "open options" in the "add vector layer" dialogue?</p>
<p>Thanks,<br />Andreas</p>
<p>On 2016-10-01 23:38, Even Rouault wrote:</p>
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0"><!-- html ignored --><!-- head ignored --><!-- meta ignored -->
<div class="pre" style="margin: 0; padding: 0; font-family: monospace">FYI: An interesting finding to improve performance of large Geopackage on a <br /> shared network drive.<br /><br /> ---------- Message transmis ----------<br /><br /> Sujet : Re: [Geopackage] Geopackage on a shared network drive<br /> Date : samedi 01 octobre 2016, 16:41:05<br /> De : Árni Geirsson via Geopackage <<a href="mailto:geopackage@lists.opengeospatial.org">geopackage@lists.opengeospatial.org</a>><br /> À : Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>><br /> CC : <a href="mailto:geopackage@lists.opengeospatial.org">geopackage@lists.opengeospatial.org</a><br /><br /> Yes! It renders very quickly after I set the read-only flag in the file<br /> explorer.<br /> That goes a long way towards solving the problem for me because as I<br /> mentioned, the team would like to use this format to store geodata, not to<br /> edit it and certainly not to make edits by multiple concurrent users.<br /> Thank you so much for your help!<br /><br /> Árni<br /><br /><br /> Árni Geirsson<br /> *Alta ehf* // +354 582 5000 // +354 897 9549<br /><a href="http://www.alta.is">www.alta.is</a> // Alta á Twitter <<a href="http://twitter.com/alta_ehf">http://twitter.com/alta_ehf</a>> // Alta á<br /> Facebook <<a href="http://www.facebook.com/pages/Alta/161795813838691?v=wall">http://www.facebook.com/pages/Alta/161795813838691?v=wall</a>><br /> Gæða- og umhverfisstefna Alta <<a href="http://alta.is/pdf/svonavinnumvid.pdf">http://alta.is/pdf/svonavinnumvid.pdf</a>><br /><br /><br /><br /><br /> On 1 October 2016 at 14:28, Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>> wrote:<br /><br />
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Le samedi 01 octobre 2016 16:02:38, Árni Geirsson a écrit :
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Hello Even<br /> Thank you for your suggestion. I made the changes you suggested, adding<br /> set SQLITE_USE_OGR_VFS=YES<br /> after<br /> set VSI_CACHE=TRUE<br /> set VSI_CACHE_SIZE=1000000<br /> but there was no difference. Was this perhaps not the right way to do it?</blockquote>
<br /> Yes, that's correct.<br /><br /> Hum actually reviewing the code, the cache mechanism is only enabled when<br /> the<br /> file is opened in read-only mode, whereas QGIS will open the file in<br /> read-write<br /> mode.<br /><br /> You could perhaps try to make the GPKG read-only in the explorer?, at least<br /> just to test if it is a promising optimization. I guess write support for<br /> the<br /> cache mechanism could be added.<br /><br /> A drawback of a cache machnism is that it would defeat the updates in<br /> SQLite<br /> to deal with concurrent editing, but anyway such mechanisms don't work very<br /> well on network shares.<br /><br />
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Anyway, I would think that I am not alone in seeing this as a problem</blockquote>
but I
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">find very little about it on the web.<br /><br /> Árni<br /><br /><br /><br /> Árni Geirsson<br /> *Alta ehf* // +354 582 5000 // +354 897 9549<br /><a href="http://www.alta.is">www.alta.is</a> // Alta á Twitter <<a href="http://twitter.com/alta_ehf">http://twitter.com/alta_ehf</a>> // Alta á<br /> Facebook <<a href="http://www.facebook.com/pages/Alta/161795813838691?v=wall">http://www.facebook.com/pages/Alta/161795813838691?v=wall</a>><br /> Gæða- og umhverfisstefna Alta <<a href="http://alta.is/pdf/svonavinnumvid.pdf">http://alta.is/pdf/svonavinnumvid.pdf</a>><br /><br /><br /><br /><br /> On 30 September 2016 at 19:52, Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>><br /><br /> wrote:
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Le vendredi 30 septembre 2016 00:44:45, Árni Geirsson via Geopackage a<br /><br /> écrit :
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Dear Geopackage developers!<br /> I look forward to the day when Geopackage replaces the old shapefiles</blockquote>
<br /> and I<br /><br />
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">thank you for your efforts. As a user in a small team that uses QGIS<br /> and shares data on a NAS box, basically a Samba server for network<br /> shares, I notice that Geopackage data loads very slowly, much more<br /> slowly than a shapefile stored in exactly the same shared folder. I<br /> have noticed this with Spatialite, too, and suspect that the problem<br /> lies with Sqlite and some problem it has with the networking</blockquote>
</blockquote>
</blockquote>
protocol.
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">Since Geopackage is intended (as I understand) as a storage and<br /> exchange format for geodata, rather than a general purpose database<br /> format like Spatialite, I was</blockquote>
<br /> hoping<br /><br />
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">that this problem could be overcome and that the Geopackage data</blockquote>
</blockquote>
</blockquote>
would
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0"><br /> load<br /><br />
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">as fast as the shapefile data from the network share. When the<br /> Geopackage and shapefile data are both on a local drive, I see no<br /> difference in the loading speed.<br /><br /> Do you think this will be solved?</blockquote>
<br /> QGIS tweaks GDAL/OGR to use a large buffer size to speed up network<br /> access to<br /> shapefiles (or any format that uses the GDAL I/O layer, which is called<br /> "VSI").<br /><br /> If you look at the qgis.bat in c:\osgeo4w\bin, you will see :<br /> set VSI_CACHE=TRUE<br /> set VSI_CACHE_SIZE=1000000<br /><br /> But by default, the OGR GPKG driver uses SQLite own I/O layer, which<br /> probably<br /> does access with a granualrity of a SQLite page size (4KBytes). You</blockquote>
</blockquote>
could
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">tried to define the<br /> SQLITE_USE_OGR_VFS=YES environmenet variable, restart QGIS and then<br /> SQLite should use the GDAL/OGR I/O layer and hopefully benefit from the<br /> buffering. I<br /> have not tried that, so not completely sure if it will improve things.<br /><br /> Even<br /><br /> --<br /> Spatialys - Geospatial professional services<br /><a href="http://www.spatialys.com">http://www.spatialys.com</a></blockquote>
</blockquote>
<br /> --<br /> Spatialys - Geospatial professional services<br /><a href="http://www.spatialys.com">http://www.spatialys.com</a><br /><br /></blockquote>
<br /> -------------------------------------------------------</div>
</blockquote>
<p> </p>
<div> </div>
</body></html>