[QGIS-Developer] Regular vs spatialite sqlite database

[a.furieri at lqt.it]

On Mon, 24 Jul 2017 11:32:17 +0200, enrico chiaradia wrote:
> My second implementation uses Spatialite DB but I found it
> surprisingly slower than shapefile ...
>
> In my last implementation, I used a mixed approach, creating both
> shapefiles to store geometries and regular sqlite db to store simple
> tables. That is fast and space saving but it generates too much 
> files.
>

Hi Enrico,

SpatiaLite simply is an extension to SQLite, so both them will
have exactly the same identical speed when handling regular
(non Spatial) Tables.

Things are slightly different when handling Spatial Tables:
1. INSERT/UPDATE: SpatiaLite thoroughly checks any Geometry
    before physically modifying the database; only geometries
    safely matching the declared Type, SRID and Dimensions are
    then accepted.
    This leads to robustly consistent and well affordable
    databases, but obviously requires some (small) extra
    time.
2. if the Spatial Table is supported by one (or more)
    Spatial Index any INSERT, UPDATE or DELETE will
    always take care of properly synchronizing the Spatial
    Index (a costly operation) so this will surely add some
    further delay.
3. read operations (SELECT): SpatiaLite is usually faster
    then Shapefiles, most notably when several layers are
    involved. Accessing a monolithic SpatiaLite DB
    containing tenths (or hundredths) layers is expected
    to be a far more efficient process than accessing many
    sparse Shapefiles.
4. and if your layers (Spatial Table) are adequately
    supported by some Spatial Index you'll easily
    discover that SpatiaLite can be surprisingly fast.

conclusion: it's a trade-off (as always).
we usually bargain some reasonable slowness when
writing so to get maximum speed when reading.
usually write operations happen rather infrequently
whilst read operations are very frequents, so the
final outcome is expected to be widely positive.

Final remark: you say us nothing about the fine
implementation details of your own Phyton code.
Both SQLite and SpatiaLite have an inherently
transactional nature: this practically means that
if you don't properly handle transactions (by
declaring BEGIN and COMMIT statements where
appropriate) all write operations (INSERT,
UPDATE and/or DELETE) can easily become slow
(_VERY_ slow, may well be deadly slow).
I'm not sure if this could be your specific
case, but carefully checking if the code
correctly handles transactions always is
a good programming practice.

bye Sandro