Hi, I can see that inefficient sorting is documented in https://gdal.org/en/stable/user/ogr_sql_dialect.html#order-by
"Note that ORDER BY clauses cause two passes through the feature set. One to build an in-memory table of field values corresponded with feature ids, and a second pass to fetch the features by feature id in the sorted order. For formats which cannot efficiently randomly read features by feature id this can be a very expensive operation." Avoid doing that, or use the SQLite dialect instead. -Jukka Rahkonen- ________________________________________ Lähettäjä: gdal-dev <[email protected]> käyttäjän Rahkonen Jukka via gdal-dev <[email protected]> puolesta Lähetetty: Maanantai 20. lokakuuta 2025 12.00 Vastaanottaja: [email protected] <[email protected]>; Andrey VI <[email protected]> Aihe: Re: [gdal-dev] OGR SQL performance issue with VRT as a source? Hi, It seems to me that sorting is the slow part. If you just need to get the job done, try ogr2ogr -sql "SELECT CAST(MAX * -1 AS INTEGER) AS depth FROM merged" sample_vrt-ogr.geojsons sample.vrt -Jukka Rahkonen- ________________________________________ Lähettäjä: gdal-dev <[email protected]> käyttäjän Andrey VI via gdal-dev <[email protected]> puolesta Lähetetty: Maanantai 20. lokakuuta 2025 11.47 Vastaanottaja: [email protected] <[email protected]> Aihe: [gdal-dev] OGR SQL performance issue with VRT as a source? Hi all.I have found a significant performance drop when using OGR SQL with VRT as a source. Test results with sample data (link, 6,8 MB).VRT as a source: $ time ogr2ogr -dialect sqlite -sql "SELECT geometry, CAST(ABS(MAX) AS INT) AS depth FROM merged ORDER BY MAX DESC" sample_vrt-sqlite.geojsons sample.vrtreal 0m3,960suser 0m0,037ssys 0m0,022s$ time ogr2ogr -sql "SELECT CAST(MAX * -1 AS INTEGER) AS depth FROM merged ORDER BY MAX DESC" sample_vrt-ogr.geojsons sample.vrtreal 3m4,108suser 0m0,057ssys 0m0,016s Shapefile as a source:$ time ogr2ogr -dialect sqlite -sql "SELECT geometry, CAST(ABS(MAX) AS INT) AS depth FROM sample ORDER BY MAX DESC" sample_shp-sqlite.geojsons sample.shpreal 0m3,627suser 0m0,036ssys 0m0,025s$ time ogr2ogr -sql "SELECT CAST(MAX * -1 AS INTEGER) AS depth FROM sample ORDER BY MAX DESC" sample_shp-ogr.geojsons sample.shpreal 0m3,731suser 0m0,052ssys 0m0,025s Since my VRTs consist of tens and hundreds of Shapefiles, OGR SQL becomes unusable for the specific task given as an example above.So I wonder what could be the reason for such a significant drop in performance in this particular case? Or maybe I’m doing something wrong?--Andrey _______________________________________________ gdal-dev mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/gdal-dev _______________________________________________ gdal-dev mailing list [email protected] https://lists.osgeo.org/mailman/listinfo/gdal-dev
