Performance improvement in SpatiaLite Provider

Topics: Data Access, SharpMap v0.9 / v1.x
Jul 7, 2010 at 6:32 AM
Edited Jul 7, 2010 at 6:33 AM
Hello, I don't know if this is the right place to discuss improvements of the sourcecode. I just give it a try. With my test data (~1 GB SpatiaLite database) I noticed very long loading times, before the map is visible the first time. Using a profiler I saw that SpatiaLite.GetExtents() takes very long (~9 seconds). This function retrieves the maximum coordinates using this query: strSQL = string.Format("SELECT max(MbrMaxY({0})) as maxy, max(MbrMaxX({0})) as maxx, min(MbrMinY({0})) as miny, min(MbrMinX({0})) as minx from {1};", _geometryColumn, _table); I wondered, if this could not be improved by using the spatial index: strSQL = string.Format("SELECT max(ymax) as maxy, max(xmax) as maxx, min(ymin) as miny, min(xmin) as minx from idx_{0}_{1};", _table, _geometryColumn); Result: ~3 seconds. Means this is 3 times faster, if you have and use a spatial index! Unfortunately the existing query is sometimes extended by a where clause. This clause cannot be applied to the spatial index. therefor here the new code, I suggest: if (UseSpatiaLiteIndex && String.IsNullOrEmpty(_defintionQuery)) { strSQL = string.Format("SELECT max(ymax) as maxy, max(xmax) as maxx, min(ymin) as miny, min(xmin) as minx from idx_{0}_{1};", _table, _geometryColumn); } else { strSQL = string.Format("SELECT max(MbrMaxY({0})) as maxy, max(MbrMaxX({0})) as maxx, min(MbrMinY({0})) as miny, min(MbrMinX({0})) as minx from {1};", _geometryColumn, _table); if (!String.IsNullOrEmpty(_defintionQuery)) strSQL += " WHERE " + DefinitionQuery; } This applies to SharpMap 0.9 and 2.0. TeDe
Coordinator
Jul 7, 2010 at 7:00 AM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.