performance query (shp provider and mssqlspatial provider)

Topics: Data Access, SharpMap Project, SharpMap v0.9 / v1.x
Jul 23, 2008 at 2:23 AM
hi JohnDiss.
I do a query test with different data provider-shp provider and mssqlspatial provider.
I use the same method ExecuteIntersectionQuery() with the same parameter.
The formmer spend less than 1 second. but the latter spend nearly 20 second.
I always believe use sql query will be more faster.
what works should i do in SQL Server?
Jul 23, 2008 at 8:04 AM
Hi cdgrain, the shapefile provider does not do true intersection tests, it only tests that the bounding boxes intersect wheras the mssqlspatial provider does true intersections. If you do not need true intersection testing you can change the sql query in ExecuteIntesectionQuery() to test the _Envelope_MinX/MinY/MaxX/MaxY against the bounding box of the  input geometry. V2 already makes the distinction between bounding box and geometry intesection queries.. hth jd
Jul 24, 2008 at 2:16 AM
thanks john.
but you misunderstanded my meaning perhaps for my poor english.
1. I know shapefile provider just do bbox intersect. so after query, i should use NTS to do geom.Contain(geom1). But, I have found that mssqlspatial provider also use bbox to intersect. because, i use point as parameter. at this circumstance, the query result should only one feature that the point is contained. but the result have more than one features. so i should use NTS again to judge geom.Contain(geom1).

2. my first post question is why shapefile provider query speed is faster than mssqlspatial provider. what further works should i do in SQL Server?
thanks again.
Jul 24, 2008 at 9:34 AM
Edited Jul 24, 2008 at 9:35 AM
Hi cdgrain,
First which version of MsSqlSpatial are you using - v2 is not quite ready, but I created v1.7.x the other day whaich has all the latest ProjNet, NTS 1.7.x, GeoAPI 1.x - so if NTS is correct MsSqlSpatial should be as well - I dont know how old the dependency versions in the trunk are. Also how many records are there and how big is each geometry; try running :
SELECT COUNT(*),  MAX(DATALENGTH(geometrycolumn)) FROM geometrytable 
ExecuteIntersectionQuery runs "ST.RelateQuery" + this.BuildSpatialQuerySuffix() + "(" + strGeom + ", 'intersects')"; which actually uses NTS to do a true intersection query testing every vertex /edge against the input geometry. Even if the input geometry is rectangular the entire geometry is tested against the rectangular geometry. see So the result _should_ be correct straight out of the database and require no further processing. The provider does this processing by default - if it is not required you must make changes to the actual sql that is sent to the database

WHERE geometrycolumn_Envelope_MinX < @maxX 
AND geometrycolumn_Envelope_MaxX > @minx
AND geometrycolumn_Envelope_MinY < @minY
AND geometrycolumn_Envelope_MaxY > @minY

but without the call to ST.RelateQuery 

It may help to rebuild the indexes on the database server - things like fragmented disks also play a massive part in database performance hth jd