Performance using GeoPackages as datasources

[StefanSchuhart]

Hi everyone,

I'm testing ldproxy to provide data via OGC api features. I am a little disappointed with the data retrieval because of the slow delivery. Running this query oaf/streets/collections/streets/items?f=json&limit=6000 to receive approximately 800 kB of data the query returns after multiple seconds ranging from 2s to 4s. Thats very slow and it gets even slower with more data. Also, it seems to me that the same query never benefits from a query cache or whatsoever, I'm used to, when running a DBMS like PostgreSQL.

I tested on an Azure VM with 2 CPUs and 8 GB of RAM. Its clear to me that this is not a sophisticated test. Are there an configuration options to speed up reading from Geopackages? Using Gepackages would be my preferred deployment method for ldproxy in container environments.

Here is a snippet from my provider configuration:

---
id: streets
entityStorageVersion: 2
providerType: FEATURE
providerSubType: SQL
nativeCrs:
  code: 25832
  forceAxisOrder: LON_LAT
typeValidation: NONE
connectionInfo:
  dialect: GPKG
  database: streets.gpkg
  pool:
    maxConnections: -1
    minConnections: 1
    initFailFast: true
    initFailTimeout: 1
    idleTimeout: 10m
    shared: false
sourcePathDefaults:
  primaryKey: fid
  sortKey: fid
queryGeneration:
  chunkSize: 10000
  computeNumberMatched: true
types:
  streets:
    label: Streets HH
    type: OBJECT
    sourcePath: /streets_v10
    properties:
      id:
        sourcePath: fid
        type: INTEGER
      geometry:
        sourcePath: geom
        type: GEOMETRY
        role: PRIMARY_GEOMETRY
        geometryType: LINE_STRING
      max_speed:
        sourcePath: max_speed
        type: FLOAT
      truck_traffic_daily:
        sourcePath: truck_traffic_daily
        type: FLOAT
      car_traffic_daily:
        sourcePath: car_traffic_daily
        type: FLOAT
      traffic_settings_adjustable:
        sourcePath: traffic_settings_adjustable
        type: INTEGER
      road_type:
        sourcePath: road_type
        type: STRING

[cportele]

@StefanSchuhart - Thank you for raising this issue. We have looked into this and analyzed requests with a profiler. We found an issue (#1284) and have updated the code and the iide/ldproxy:next image.

This has improved feature requests with a GeoPackage backend significantly, when many features are requested. For testing I have used GeoJSON feature requests in the Zoomstack API (collection "roads_local) deployed on my notebook (maximum limit increased to 100000). The API has a GeoPackage feature store. Results:

• 10000 features (2,3 MB): 2.6s (without fix) to 1.3s (with fix)
• 100000 features (25.8 MB): 13.5s (without fix) to 6.0s (with fix)

We see some potential for additional optimizations in the pipeline that processes the SQL results and encodes the data in GeoJSON, but overall the profiling results now look as we would have expected them.

I have also updated https://demo.ldproxy.net/. Accessing that remote API with curl "https://demo.ldproxy.net/zoomstack/collections/roads_local/items?f=json&limit=10000" > roads_local.json results for me in response times between 1.5s to 1.8s.

I am not an SQLite expert and I do not have any recommendations how to potentially speed up queries from a GeoPackage.

Does this help?

[StefanSchuhart]

@cportele - Thank for your reply and the changes you made to improve read speeds! I will give it a try.

I am not an sqlite expert either. But in the meantime I had a quick look at this article: https://phiresky.github.io/blog/2020/sqlite-performance-tuning/ and from there it looks like when connecting to Geopackages there are some settings the client (in this case ldproxy) could apply to improve read speeds.