[gdal-dev] SQLite/SpatiaLite slowness in OGR

[Even Rouault]

Hi Sandro,

I'm OK with your patch. I guess that the name of the GML_SQLITE_CACHE_MB config 
option must come from your work with your previous GML patch, but I'd suggest 
to rename it to OGR_SQLITE_CACHE as it is more general. Are you OK with that ? 
I'll also add a note in ogr2ogr man page about the -gt option, moving part of 
your proposed doc in it.

By the way, you didn't react to my comments on your GML patch. Perhaps you are 
working on a revised patch ?

Best regards,

Even

> 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