GeoPackage – Why Editing a GeoPackage Table with OGR is Very Slow

Question

I want to edit some fields in a GeoPackage (download a.gpkg)

# -*- coding: utf-8 -*-
from osgeo import ogr
from time import clock

print ("Start",clock())
source = ogr.Open("c:/.../a.gpkg",update=True)
layer = source.GetLayerByName( "263c6845-e2c3-4eee-ae26-f0e9539f3c2bP" )
laydef = layer.GetLayerDefn()

i=0
feature = layer.GetNextFeature()
while feature:
    i=i+1
    # make some stuff
    # .....
    layer.SetFeature(feature)
    feature = layer.GetNextFeature()
source.Destroy()
print ("Finish",i,clock())

('Start', 3.4136520136791866e-07) ('Finish', 1612, 243.56666043106233)

It works, but it takes a long time (4 minutes for 1.600 records).

What is going wrong?

Answer 1

Make a minor but important change and make all edits to happen within one transaction instead of doing one transaction for each row. Transactions are rather expensive for SQLite and therefore also for GeoPackage. Making less but bigger transactions is faster. You can find some numbers from https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite.

A modified script:

# -*- coding: utf-8 -*-
from osgeo import ogr
from time import clock

print ("Start",clock())
source = ogr.Open("a.gpkg",update=True)
layer = source.GetLayerByName( "263c6845-e2c3-4eee-ae26-f0e9539f3c2bP" )
laydef = layer.GetLayerDefn()
layer.StartTransaction()
i=0
feature = layer.GetNextFeature()
while feature:
    i=i+1
    # make some stuff
    # .....
    layer.SetFeature(feature)
    feature = layer.GetNextFeature()
layer.CommitTransaction()
source.Destroy()
print ("Finish",i,clock())

Compare the timings:

With one transaction

python run_a.py
('Start', 7.292488590901599e-07)
('Finish', 1612, 0.09405851784544883)

Original script without transaction:

python run_a.py
('Start', 7.292488590901599e-07)
('Finish', 1612, 15.54201567301648)

I have SSD disk which may explain why your original script is considerably faster for me (15.5 sec vs. 243.56 sec).