Performance Questions / Comments on FilterQuery

Jul 2, 2008 at 4:10 PM
I have a query is taking a rather long time to run (over 6 seconds). I have used similar queries without problems in the past, but now on this specific one I am seeing quite a bit of slowness.

SELECT

the_geom, jurisdiction
FROM ST.FilterQuery#dbo#Jurisdictions#the_geom(ST.MakeEnvelope(-67.8601953125,17.35109375,-64.9598046875,19.10890625, 4326))
WHERE JurisdictionLevelID = 36 and AgencyID = 6

Does anyone have any suggestions on how to improve this query?

Since the data is already stored in EPSG 4326, I was able to rewrite the query in the following format and get better performance:

 

 

SELECT the_geom, jurisdiction
FROM Jurisdictions
WHERE AgencyID = 6
    AND JurisdictionLevelID = 36 
    
AND the_geom IS NOT NULL
    
AND the_geom_Envelope_MinX < -64.9598046875 AND the_geom_Envelope_MaxX > -67.8601953125
    
AND the_geom_Envelope_MinY < 19.10890625 AND the_geom_Envelope_MaxY > 17.35109375

Total RowCount is 1032
Version 1 runs in 6.76 seconds and Version 2 runs in 0.34 seconds. Both return 771,053 bytes to the client.

That is a 20x performance difference.

Changing the AgencyID and JurisdictionLevelID significantly varies the performance of both queries based on the number of rows returned however, there is consistently a 20 to 100X performance difference in the two versions.

Any suggestions would be appreciated.

Jon

 

Developer
Jul 4, 2008 at 9:16 AM
Edited Jul 4, 2008 at 9:24 AM
Hi Jon, Filtering using the _Envelope fields allows sql server to use the indexes. Using the filter functions require parsing the input wkb and the geometry field for each row in the datatable and doing an intersection test which requires considerably more processing. There is a MsSqlGeometry UDT in the v2 dev branch (which is probably not ready for production especially if you require reprojection/ and / or work with very large datasets in a memory constrained environment..) which may improve performance a bit but requires that the geometry field wkb be less than 8kb - using it will complicate upgrades though so beware. HTH jd

Correction: the input geometry is parsed only once when using the FilterQuery but every row if doing it by hand e.g ST.Intersects(geometry_column, ST.MakeEnvelope(x1,y1,x2,y2))

ps: add indexes to AgencyID and JurisdictionLevelID and you should see even better performance of your second query..
Jul 7, 2008 at 3:35 PM
Thanks for the info. I had not taken the time to look at the implementation of the FilterQuery and other Table-valued Functions and had incorrectly assumed they took advantage of the indexes to determine which rows were candidates before processing the wkb.

Along the lines of the UDT you mentioned adding for V2 is there a roadmap for what is planned for V2 and how MSSQLSPATIAL fits with SQL Server 2008. I several customizations in our build (mostly for importing from other data sources) that I would not mind contributing back if they fit in with the plan for the next version.
Developer
Jul 8, 2008 at 10:03 AM
Hi again Jon, so far the main work on v2 has been done to update the underlying libraries to the latest versions of NTS, GeoAPI, SharpMap alongside the development of these. The UDTs were an experiment to see if the performance of operations could be improved, and to an extent they work but with limitations. In Sql Server 2008 the UDT size limit can be removed but they will still be unable to use true spatial indexes.  WRT a roadmap, MsSqlSpatial is likely to add support for topological/projection/conversion operations on native sql 2008 geometry datatypes as well as maintaining the existing featureset. The new MsSqlSpatial UDTs may possibly play a part in this, but this remains to be seen.. hth jd