[gdal-dev] Changing many features in a GPKG in a transaction, why is the index updated?

[Nyall Dawson]

On Wed, 12 Jun 2024 at 11:54, Even Rouault <even.rouault at spatialys.com>
wrote:

>
> But... as you mention "bulk attribute changes", assuming you don't
> change geometries, you would be better using OGR_L_UpdateFeature() where
> you could specify to update only a subset of attributes instead of
> updating the whole feature. In that case, the RTree triggers would not
> run.

Thanks for the pointer Even -- indeed switching to OGR_L_UpdateFeature does
give a massive performance boost and avoids the unwanted index updates!
This is great!

> That said even if the update RTree trigger runs, it checks that the
> geometry hasn't changed, so as you mention rtreeUpdate running, I
> suspect you change the geometries as well, in which case I have no
> suggestion.

There was definitely no (intentional) geometry modification happening 🤷

Nyall

>
> Even
>
> Le 12/06/2024 à 03:30, Nyall Dawson via gdal-dev a écrit :
> > Hi list,
> >
> > I'm trying to understand some unexpected behavior I'm seeing while
> > profiling bulk attribute changes in a GPKG file.
> >
> > First, I start a transaction on the layer using
> > OGR_L_StartTransaction. This is successful.
> > I then make many calls to OGR_L_SetFeature, changing different
> > features, before finalizing the transaction with
OGR_L_CommitTransaction.
> >
> > Now, when profiling this code, I'm seeing that
> > OGRGeoPackageTableLayer::ISetFeature(OGRFeature*) is taking a very
> > large chunk of time. This is due to the call to sqlite3_step at
> >
https://github.com/OSGeo/gdal/blob/2b94d7a274bfeb881a176016de932ba8acd1b79a/ogr/ogrsf_frmts/gpkg/ogrgeopackagetablelayer.cpp#L3134
> > triggering a call to rtreeUpdate within sqlite3.
> >
> > I'm confused as to why this is happening. If I'm in a transaction,
> > shouldn't the index update be deferred until the transaction is
> > committed? Why would it be updating after every individual update?
> >
> > Hoping someone can explain what I'm missing here...
> > Nyall
> >
> > _______________________________________________
> > gdal-dev mailing list
> > gdal-dev at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/gdal-dev
>
> --
> http://www.spatialys.com
> My software is free, but my time generally not.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20240612/c258e971/attachment-0001.htm>