On Mon, 19 Oct 2020 at 09:56, Even Rouault <[email protected]> wrote: > > On dimanche 18 octobre 2020 15:35:11 CEST Nyall Dawson wrote: > > On Sat, 17 Oct 2020 at 20:02, Even Rouault <[email protected]> > wrote: > > > Hi Nyall, > > > > > > > But I can't explain this. In both cases the resultant gpkg has a > > > > spatial index. Does anyone know why one of the methods is so much > > > > slower than the other? (And ultimately, can we fix > > > > QgsVectorLayerExporter so it uses the same fast approach!) > > > > > > Candidate fix and explanations at: > > > https://github.com/qgis/QGIS/pull/39439 > > > > Thanks Even! > > > > Just for my own curiosity, is there some logic in GDAL which defers > > the spatial index creation on the immediate first use of a newly > > created geopackage layer? > > This isn't a GDAL core mechanism (since GDAL has no abstraction for a spatial > index), but a possibly per-driver optimization. Which is implemented in the > GPKG driver. On a freshly new created GeoPackage layer (if you don't close and > reopen the dataset), the spatial index creation will be deferred until the > first call of GetNextFeature() or SyncToDisk() (which will happen if you close > the dataset or run ExecuteSQL()).
Ok, makes sense. Thanks for the explanation! > For the update/append scenario to an existing table, one thing that can help > is to run "PRAGMA synchronous = OFF". For a freshly new created database, > PRAGMA synchronous = OFF is executed, which avoids fsync() calls. This is OK > for a new database as there's no prior existing data that could be corrupted > if things go wrong. For an existing one, PRAGMA synchronous = OFF would be > more risky (that said, according to https://www.sqlite.org/ > pragma.html#pragma_synchronous, QGIS crashing would still be OK w.r.t data > integrity, but not if the operating system crashes or computer is halted in a > unclean way). From a test on a 2 GB file appended into another one of the same > size, on a system with a rather slow rotational disk, PRAGMA synchronous = OFF > makes ogr2ogr -append run 4 times faster. > > Another improvement is to defer the update of the spatial index when appending > features. I've implemented it in > https://github.com/OSGeo/gdal/pull/3079. It can make append time to be divided > by 1.5 when inserting features in large batch within transactions (with > synchronous = OFF enabled, otherwise I'm not patient enough :-)) Wow, great! So now Tim's screencast has directgly lead to a bunch of improvements from GDAL up :D (bring on the next one Tim!) Nyall > > Even > > -- > Spatialys - Geospatial professional services > http://www.spatialys.com _______________________________________________ QGIS-Developer mailing list [email protected] List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
