back to 4.1.0-doc
Speed optimizations introduced in 4.0.1-trunk
Many spatial operators has been substantially revised and are now more efficient than before.Basically the introduced optimizations fall in two different areas:
- preemptive evaluation of both MBRs before actually performing a Spatial comparison between two Geometries.
in many cases the result of the Spatial comparison can be easily predicted by simply considering the MBRs alone, thus avoiding to waste time while attempting to perform a more precise (and costly) evaluation. - supporting GEOS Prepared Geometry whenever is possible.
Please note well: supporting Prepared Geometries absolutely requires introducing a block of global memory, thus making any SQLite / SpatiaLite connection to be intrinsically not reentrant and thus thread unsafe.
Comparative benchmark
The following table reports timings measured on the same platform performing twice the same identical SQL query:- the first test has been executed using libspatialite 4.0.0 (not optimized).
- the second test has been executed using libspatialite-4.0.1-trunk (current development version, implementing the optimizations).
- the same identical DB-file was connected in both cases.
Any possible precaution has been adopted so to performed each test under hot cache conditions.
As you can easily notice, the actual efficiency of the introduced optimizations is rather variable and strongly affected be the specific query context:
- as a rule of the thumb the speed-up effect is absolutely impressive when no Spatial Index is used at all.
- when a Spatial Index is explicitly invoked the benefits are less evident, and sometimes they are completely negligible.
- anyway the overall impact of the introduced optimizations is surely interesting and worthwhile to be adopted in the next release.
Please note: all timings are reported in minutes and seconds
| SQL Query | not optimized 4.0.0 |
optimized 4.0.1-trunk |
|---|---|---|
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_Contains(p.geometry, c.geometry) = 1 AND p.cod_reg = 9; |
1:17 | 0:31 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_Within(c.geometry, p.geometry) = 1 AND p.cod_reg = 3; |
1:02 | 0:24 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_Covers(p.geometry, c.geometry) = 1 AND p.cod_reg = 8; |
0:56 | 0:23 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_CoveredBy(c.geometry, p.geometry) = 1 AND p.cod_reg = 15; |
0:43 | 0:17 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_Contains(p.geometry, c.geometry) = 1 AND c.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'com2011' AND search_frame = p.geometry ); |
1:23 | 0:46 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_Within(c.geometry, p.geometry) = 1 AND c.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'com2011' AND search_frame = p.geometry ); |
1:23 | 0:46 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_Covers(p.geometry, c.geometry) = 1 AND c.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'com2011' AND search_frame = p.geometry ); |
1:23 | 0:46 |
|
SELECT p.nome_pro, c.nome_com FROM prov2011 AS p, com2011 AS c WHERE ST_CoveredBy(c.geometry, p.geometry) = 1 AND c.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'com2011' AND search_frame = p.geometry ); |
1:23 | 0:46 |
|
SELECT p1.nome_pro, p2.nome_pro FROM prov2011 AS p1, prov2011 AS p2 WHERE ST_Touches(p1.geometry, p2.geometry) = 1; |
0:56 | 0:43 |
|
SELECT p1.nome_pro, p2.nome_pro FROM prov2011 AS p1, prov2011 AS p2 WHERE ST_Touches(p1.geometry, p2.geometry) = 1 AND p1.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'prov2011' AND search_frame = p2.geometry ); |
0:33 | 0:33 |
|
SELECT p1.nome_pro, p2.nome_pro FROM prov2011 AS p1, prov2011 AS p2 WHERE ST_Equals(p1.geometry, p2.geometry) = 1; |
0:40 | 0:27 |
|
SELECT p1.nome_pro, p2.nome_pro
FROM prov2011 AS p1, prov2011 AS p2 WHERE ST_Equals(p1.geometry, p2.geometry) = 1 AND p1.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'prov2011' AND search_frame = p2.geometry ); |
0:17 | 0:16 |
back to 4.1.0-doc