[gdal-dev] Does GDAL use prepared statements with Spatialite?

[a.furieri at lqt.it]

On Mon, 10 Mar 2014 12:23:22 +0000 (UTC), Jukka Rahkonen wrote:
> Hi,
>
> I saw a blog about using prepared statements for bulk inserts into 
> SQLite
> http://blog.quibb.org/2010/08/fast-bulk-inserts-into-sqlite/
>
> Results show that with prepared statements the inserts/second rate in 
> the
> test were at least 3 times better than with standard transactions
> 
> http://blog.quibb.org/wp-content/uploads/2010/07/inserts_per_second.png
>

Hi Jukka,

that's absolutely not surprising, and it's easy explaining why
all this happens.

a) consider a statement like this:
    INSERT ... VALUES ('alpha', 3, 'beta', 2.5, 'gamma', NULL);

    the SQL engine is then forced to parse the whole input line,
    identifying each token, checking for overall correctness and
    eventually converting values into the most appropriate datatype
    (e.g. 2.5 will internally become a floating point value).
    and all these steps will be repeated yet again for every
    inserted row.


b) now consider how the "prepared statement" works:
    INSERT ... VALUES (?, ?, ?, ?, ?)

    the SQL engine will now evaluate only once the statement,
    just at the very beginning of the process, and will return
    a pointer referencing a persistent sqlite3_stmt object

    sqlite3_bind_text   (stmt, 0, "alpha", -1, SQLITE_STATIC);
    sqlite3_bind_int    (stmt, 1, 3);
    sqlite3_bind_text   (stmt, 2, "beta", -1, SQLITE_STATIC);
    sqlite3_bind_double (stmt, 3, 2.5);
    sqlite3_bind_null   (stmt, 4);

    for each actual INSERTed row now the SQL engine will just
    replace the passed values into the requested ordinal positions.
    there is no further need to parse yet again the statement,
    and there is no type conversion at all, because the caller
    will now take full responsibility about passed values and
    corresponding data-types.
    by far less computations are involved, and this clearly
    explains why it's a much more effective approach.


> Could prepared statements make similar boost for saving spatial
> data into SQLite/Spatialite/GeoPackage?
>

surely yes: e.g. continuously converting back and forth from WKT/WKB
to the internal binary BLOB format certainly imposes a rather heavy
computational load.
directly passing binary BLOB geometries ensures better performances,
has a narrowest memory footprint and fully preserves numeric
precision thus completely avoiding any rounding/truncation.


> Or does GDAL SQLite/Spatialite driver already utilize them?
>

Even already answered to this question: yes.
I can simply add that both SpatiaLite itself and the QGIS/spatialite
data-provider widely adopt the faster "prepared statements" approach
whenever is possible.

bye Sandro