[mapserver-users] Re: Mapserver unnecessary slow with Spatialite

[Rahkonen Jukka]

Even Rouault wrote:
> 30. August 2011 23:28

> I've just added support for spatialite views in GDAL trunk 
> (r23011). Spatial 
> index of the base table is used to speed up spatial queries 
> on the view.
> 
> On your example, to create the spatial view :
> 
> ogrinfo berlin.sqlite -sql "create view 
> osm_line_highway_not_null as select 
> OGC_FID AS ROWID, GEOMETRY, osm_id ,highway,ref,name, tunnel 
> from osm_line 
> where highway is not null"
> 
> ogrinfo berlin.sqlite -sql "INSERT INTO 
> views_geometry_columns (view_name, 
> view_geometry, view_rowid, f_table_name, f_geometry_column) VALUES 
> ('osm_line_highway_not_null', 'GEOMETRY', 'ROWID', 
> 'osm_line', 'GEOMETRY')"
> 
> Note: you must call the primary key ROWID if you want QGIS to 
> be able to 
> display the view on the map. I'm not sure why this is 
> necessary (the OGR 
> driver works with other names), may be a limitation of the 
> qgis spatialite 
> driver.
> 
> And then you can use  osm_line_highway_not_null as a regular 
> layer for 
> mapserver.

I have done some speed tests with fresh Mapserver Windows build (date 09.09.2011) installed from
http://www.gisinternals.com/sdk/PackageList.aspx?file=release-1500-gdal-mapserver.zip
and the gdal binaries from the same package.

Results are somehow baffling. Good news is that with the new build does support the use of Spatialite views as Mapserver layers that the views are really a bit faster to use than having DATA as "SELECT...FROM...WHERE.." in the mapfile. Feels good so far. But when I switched back to standard MS4W I measured that Mapserver was more than two times faster with the old binaries than with the new ones.
A closer analysis showed that new binaries were considerably faster in rendering the most heavy layer "roadsclose_03" but much slower with all the othet layers. 

Results with MS4W
=================
 rendering using outputformat named png (AGG/PNG).
 WMS/WFS set-up and query, 0.000s
 Layer 0 (landuse_layer1), 0.531s
 Layer 1 (landuse_layer2), 0.481s
 Layer 2 (landuse_layer3), 0.501s
 Layer 3 (landuse_layer4), 0.550s
 Layer 4 (landuse_layer5), 0.491s
 Layer 5 (landuse_layer6), 0.281s
 Layer 7 (sports), 0.430s
 Layer 10 (railways_03), 0.301s
 Layer 18 (roadsclose_03), 5.398s
 Layer 27 (places_01), 0.250s
 Drawing Label Cache, 0.010s
 total time: 9.244s

Results with the dev-binaries
=============================
 rendering using outputformat named png (AGG/PNG).
 WMS/WFS set-up and query, 0.000s
 Layer 0 (landuse_layer1), 1.753s
 Layer 1 (landuse_layer2), 2.554s
 Layer 2 (landuse_layer3), 1.762s
 Layer 3 (landuse_layer4), 1.733s
 Layer 4 (landuse_layer5), 2.543s
 Layer 5 (landuse_layer6), 1.793s
 Layer 7 (sports), 2.163s
 Layer 10 (railways_03), 1.732s
 Layer 18 (roadsclose_03), 1.753s
 Layer 27 (places_01), 1.732s
 Drawing Label Cache, 0.000s
 total time: 19.518s


I can mail a link for downloading the whole test set-up with data and mapfiles if someone gets interested.

-Jukka Rahkonen-