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).

Answer 2

What is going on is that the features in the GeoPackage file are modified directly i.e. the code writes to the file when it is calling layer.SetFeature(feature) in the loop. What you should do is to create a copy of the layer to the memory, edit the features in the memory layer and copy the memory layer back to the GeoPackage file.

Test with the following code took about 4 seconds with the a.gpkg file:

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

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

driver_mem = ogr.GetDriverByName('MEMORY')
source_mem = driver_mem.CreateDataSource('memData')
#open the memory datasource with write access
tmp_mem = driver_mem.Open('memData',update=True)
#copy a layer to memory
layer_mem = source_mem.CopyLayer(layer,'263c6845-e2c3-4eee-ae26-f0e9539f3c2bP',['OVERWRITE=YES'])

i=0
for feature in layer_mem:
    i=i+1
    # make some stuff
    # ....
    layer.SetFeature(feature)

source.Destroy()

driver_dest = ogr.GetDriverByName('GPKG')
source_dest = driver_dest.CreateDataSource('c:/.../a.gpkg')
tmp_dest = driver_dest.Open('c:/.../a.gpkg',update=True)
layer_dest = source_dest.CopyLayer(layer_mem,'263c6845-e2c3-4eee-ae26-f0e9539f3c2bP',['OVERWRITE=YES'])

source_mem.Destroy()
source_dest.Destroy()
print ("Finish",i,clock())