[Qgis-developer] Fwd: Re: [Geopackage] Geopackage on a shared network drive

[Even Rouault]

FYI: An interesting finding to improve performance of large Geopackage on a 
shared network drive.

----------  Message transmis  ----------

Sujet : Re: [Geopackage] Geopackage on a shared network drive
Date : samedi 01 octobre 2016, 16:41:05
De : Árni Geirsson via Geopackage <geopackage at lists.opengeospatial.org>
À : Even Rouault <even.rouault at spatialys.com>
CC : geopackage at lists.opengeospatial.org

Yes! It renders very quickly after I set the read-only flag in the file
explorer.
That goes a long way towards solving the problem for me because as I
mentioned, the team would like to use this format to store geodata, not to
edit it and certainly not to make edits by multiple concurrent users.
Thank you so much for your help!

Árni


Árni Geirsson
*Alta ehf* // +354 582 5000 // +354 897 9549
www.alta.is  // Alta á Twitter <http://twitter.com/alta_ehf> // Alta á
Facebook <http://www.facebook.com/pages/Alta/161795813838691?v=wall>
Gæða- og umhverfisstefna Alta <http://alta.is/pdf/svonavinnumvid.pdf>




On 1 October 2016 at 14:28, Even Rouault <even.rouault at spatialys.com> wrote:

> Le samedi 01 octobre 2016 16:02:38, Árni Geirsson a écrit :
> > Hello Even
> > Thank you for your suggestion. I made the changes you suggested, adding
> > set SQLITE_USE_OGR_VFS=YES
> > after
> > set VSI_CACHE=TRUE
> > set VSI_CACHE_SIZE=1000000
> > but there was no difference. Was this perhaps not the right way to do it?
>
> Yes, that's correct.
>
> Hum actually reviewing the code, the cache mechanism is only enabled when
> the
> file is opened in read-only mode, whereas QGIS will open the file in
> read-write
> mode.
>
> You could perhaps try to make the GPKG read-only in the explorer?, at least
> just to test if it is a promising optimization. I guess write support for
> the
> cache mechanism could be added.
>
> A drawback of a cache machnism is that it would defeat the updates in
> SQLite
> to deal with concurrent editing, but anyway such mechanisms don't work very
> well on network shares.
>
> > Anyway, I would think that I am not alone in seeing this as a problem
> but I
> > find very little about it on the web.
> >
> > Árni
> >
> >
> >
> > Árni Geirsson
> > *Alta ehf* // +354 582 5000 // +354 897 9549
> > www.alta.is  // Alta á Twitter <http://twitter.com/alta_ehf> // Alta á
> > Facebook <http://www.facebook.com/pages/Alta/161795813838691?v=wall>
> > Gæða- og umhverfisstefna Alta <http://alta.is/pdf/svonavinnumvid.pdf>
> >
> >
> >
> >
> > On 30 September 2016 at 19:52, Even Rouault <even.rouault at spatialys.com>
> >
> > wrote:
> > > Le vendredi 30 septembre 2016 00:44:45, Árni Geirsson via Geopackage a
> > >
> > > écrit :
> > > > Dear Geopackage developers!
> > > > I look forward to the day when Geopackage replaces the old shapefiles
> > >
> > > and I
> > >
> > > > thank you for your efforts. As a user in a small team that uses QGIS
> > > > and shares data on a NAS box, basically a Samba server for network
> > > > shares, I notice that Geopackage data loads very slowly, much more
> > > > slowly than a shapefile stored in exactly the same shared folder. I
> > > > have noticed this with Spatialite, too, and suspect that the problem
> > > > lies with Sqlite and some problem it has with the networking
> protocol.
> > > > Since Geopackage is intended (as I understand) as a storage and
> > > > exchange format for geodata, rather than a general purpose database
> > > > format like Spatialite, I was
> > >
> > > hoping
> > >
> > > > that this problem could be overcome and that the Geopackage data
> would
> > >
> > > load
> > >
> > > > as fast as the shapefile data from the network share. When the
> > > > Geopackage and shapefile data are both on a local drive, I see no
> > > > difference in the loading speed.
> > > >
> > > > Do you think this will be solved?
> > >
> > > QGIS tweaks GDAL/OGR to use a large buffer size to speed up network
> > > access to
> > > shapefiles (or any format that uses the GDAL I/O layer, which is called
> > > "VSI").
> > >
> > > If you look at the qgis.bat in c:\osgeo4w\bin, you will see :
> > > set VSI_CACHE=TRUE
> > > set VSI_CACHE_SIZE=1000000
> > >
> > > But by default, the OGR GPKG driver uses SQLite own I/O layer, which
> > > probably
> > > does access with a granualrity of a SQLite page size (4KBytes). You
> could
> > > tried to define the
> > > SQLITE_USE_OGR_VFS=YES environmenet variable, restart QGIS and then
> > > SQLite should use the GDAL/OGR I/O layer and hopefully benefit from the
> > > buffering. I
> > > have not tried that, so not completely sure if it will improve things.
> > >
> > > Even
> > >
> > > --
> > > Spatialys - Geospatial professional services
> > > http://www.spatialys.com
>
> --
> Spatialys - Geospatial professional services
> http://www.spatialys.com
>

-------------------------------------------------------
-- 
Spatialys - Geospatial professional services
http://www.spatialys.com