[gdal-dev] Add a new performance hint for Spatialite

[Jukka Rahkonen]

Hi,

I took timings about adding 115 GML files (548 MB together, 3.2 million
linestrings) into a Spatialite table. With default settings the table gets
initialized with spatial index which makes following inserts slower. Another
alternative is to create the table without spatial index, append all the
data first and as a last step create spatial index for the ready made table.

With spatial index on:
Append + index: 71 minutes

With spatial index off:
Append: 9 minutes
Create spatial index: 6 minutes
Total: 15 minutes

I was rather happy with the initial conversion speed until I made this test
which revealed that creating spatial index as a final step made the whole
process more than four times faster! This way both data table and spatial
index are probably in contiguous chunks in the SQLite datafile and there is
no need for post process VACUUM. Vacuuming in SQLite is rather slow and for
this 1.3 GB database it takes more than 4 minutes to run.

Suggestion: Add a new performance hint on page
http://www.gdal.org/ogr/drv_sqlite.html

"If many source files will be collected into the same Spatialite table it
can be much faster to initialize the table without a spatial index by using
-lco SPATIAL_INDEX=NO and to create spatial index with a separate command
after all the data are appended. Spatial index can be created with ogrinfo
command
ogrinfo db.sqlite -sql "SELECT
CreateSpatialIndex('table_name','geometry_column_name')"

Perhaps it could also be mentioned as a performance hint that VACUUUM can
also be done from orginfo as
ogrinfo db.sqlite -sql "VACUUM"

-Jukka Rahkonen-