[gdal-dev] OGR SQL performance issue with VRT as a source?

[Even Rouault]

Hi,

https://github.com/OSGeo/gdal/pull/13254 will dramatically improve the 
performance for OGRVRTUnionLayer + OGR SQL with ORDER. The reason for 
the current slowness is that OGR SQL with ORDER does a full scan to 
collect the column value to be sorted and the feature ID, then sort, and 
then use GetFeature() over the sorted list to retrieve features by ids. 
In the case of a OGRVRTUnionLayer before above PR, the implementation 
was inefficient and would cause a linear scan from FID 0 to be done for 
each queried feature.  In the SQLite case, I believe that SQLite must 
create some sort of temporary table with the content of collected 
features, which doesn't require reading them back from the source.

Even

Le 20/10/2025 à 10:47, Andrey VI via gdal-dev a écrit :
> Hi all.
>
> I have found a significant performance drop when using OGR SQL with 
> VRT as a source. Test results with sample data (link 
> <https://drive.google.com/file/d/1pmwTACFtxjbNDpwuqQJXl0pbtGO6RqeJ/view?usp=sharing>, 
> 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.vrt
> real    0m3,960s
> user    0m0,037s
> sys     0m0,022s
> $ time ogr2ogr -sql "SELECT CAST(MAX * -1 AS INTEGER) AS depth FROM 
> merged ORDER BY MAX DESC" sample_vrt-ogr.geojsons sample.vrt
> real    3m4,108s
> user    0m0,057s
> sys     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.shp
> real    0m3,627s
> user    0m0,036s
> sys     0m0,025s
> $ time ogr2ogr -sql "SELECT CAST(MAX * -1 AS INTEGER) AS depth FROM 
> sample ORDER BY MAX DESC" sample_shp-ogr.geojsons sample.shp
> real    0m3,731s
> user    0m0,052s
> sys     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
> 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/20251020/0293e407/attachment.htm>