[gdal-dev] SQLite/SpatiaLite slowness in OGR

[Alessandro Furieri]

Hi,

I noticed that ogr2ogr is unnecessarily slow while
accessing really huge SpatiaLite datasources;
the 'standard' default settings aren't always
the most appropriate to get best performance.

- inserting many rows [hundredth thousand or
   even millions] in the same table/layer is a
   really slow process.
   the reason accounting for such slowness is
   to be identified on too much frequent COMMITs,
   this causing a noticeable performance degradation.

   the -gt arg [group features per transaction]
   is set to a 200 value by default; simply setting
   an explicit -gt 1024 will actually ensure a
   performance boost of 3X
   and defining a bigger value, i.e. -gt 65536,
   can ensure a performance boost of 4X

   this performance-related issue isn't referenced at
   all in the current OGR documentation; may be adding
   a short note could be really useful for many users.


- usually SQLite adopts a very small internal page cache:
   the default setting is 2000 pages (this corresponding
   to only 20MB of RAM allocation).

   under many circumstances defining a bigger internal
   cache (e.g. 512MB, or even 1GB) helps a lot while
   heavily accessing some really huge DB-file.
   this is most notably true when a Spatial Index is
   involved.

   introducing a configuration option GML_SQLITE_CACHE_MB xx
   will allow users to flexibly configure an appropriate
   internal Page Cache.

Please, consider adopting the suggested patch.

bye Sandro
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sqlite-cache.zip
Type: application/octet-stream
Size: 1899 bytes
Desc: not available
Url : http://lists.osgeo.org/pipermail/gdal-dev/attachments/20111211/94fc6d68/sqlite-cache.obj