How to optimize performance of db view based on STIntersect?

Question

Hello all,

I have a grand master plan for how I want to set up our MS SQL database when we migrate our data this summer, and part of that includes having a MASTER database that contains base data (like parcels, roads, cities) that I show as views in other databases based on various geographies. I have written a query that does this using parcels from my MASTER_GIS database and a boundary from OTHER_GDB and stores the view in OTHER_GDB. In ArcMap, however, it runs painfully slow (when it draws at all). See said SQL query below:

USE OTHER_GDB

GO

CREATE VIEW dbo.parcelView

AS

SELECT parc.OBJECTID, parc.PROP_ID, parc.SITE_ADDR, parc.SHAPE

FROM MASTER_GIS.dbo.PARCELS AS parc

JOIN dbo.BOUNDARY AS boundary

ON

parc.SHAPE.STIntersects(boundary.SHAPE) = 1

My question: Is it possible to accomplish this and end up with usable feature class in ArcMap?

ForrestChevaillier1 · Answer

Thanks for the responses. I should have mentioned initially - I am a complete novice, so please forgive my ignorance. I’ve only been playing with SQL Server 2012 Express for about three weeks.

Based on your info, I changed the query to use a simpler boundary file and used Filter() instead of STIntersect(). The view now draws and I can do selections. Though it is still a little slower than the unaltered parcels feature class, it sounds like that’s the best I can hope for when attempting cross-database queries - at least until I learn more about optimization. I'm realizing I need to crawl before I run. The new query is:

CREATE VIEW dbo.parcView

AS

SELECT parc.OBJECTID, parc.PROP_ID, parc.SITE_ADDR, parc.SHAPE

FROM MASTER_GIS.dbo.PARCELS AS parc

JOIN dbo.SIMPLE_BOUNDARY AS bdry

ON

parc.SHAPE.Filter(bdry.SHAPE) = 1

Here are the stats (or at least some of them, omissions made where my answer is a simple shrug):

* How complex the "BOUNDARY" geometry is (number of vertices)

1607 vertices (the original boundary file had 4125)

* The exact time it takes to query BOUNDARY from a SQL prompt

I put just the SELECT query in a new query in SSMS and the time said 00:00:01

* The exact time it takes to draw the view in ArcMap.

I don’t know how to measure this – would love to learn though.

* The spatial index options used on the PARCELS geometry column index

The default spatial index that is added when you import data, I don’t know how to look at the options or alter the index right now. Something I will look into.

I am very interested in trying the tessellation method. I need to Google about 6000 terms before I even understand what that means, but I’ll get there eventually!

JoshuaBixby · Answer

Some of SQL Server's spatial operations are known to be sensitive to the number of vertices in the polygons involved in the operations, at least this was the case with SQL Server 2008, 2008R2, and 2012 (I haven't revisited the issue with 2014 or newer).  There are numerous MSDN threads that discuss various aspects of this issue.  Some that come to mind are:

* SQL 2K8R2: Any performance hints for bulk loading data from spatial/GIS db into a data warehouse?​
* SQL Server Express Performance Limitations With OGC Methods on Geometry Instances​
* slow spatial predicates (STContains, STIntersects, STWithin, ...)​

The titles might not sound relevant, but there is some good content in all three of those threads.  Personally, I have used the tessellation method put forward by Jakub K. in the aforementioned threads, and it dramatically improved performance.

If you can live with fuzzy results, i.e., a few false positives around the peripheries of your boundaries, it might be worth looking into Filter instead of STIntersects because Filter is a "fast, index-only intersection method."

Of course, tuning spatial indexes can also sometimes improve performance instead of relying on the default spatial indexes that have been chosen for you when creating the feature classes.

VinceAngelo · Answer

Cross-database queries will usually have poor performance, which is part of the reason they're not supported by ArcGIS.

In order to determine if anything could be done to improve this query, we'd need to know:

* How complex the "BOUNDARY" geometry is (number of vertices)
* The ratio of the area of the envelope of BOUNDARY the area of the envelope of PARCELS
* The exact time it takes to query BOUNDARY from a SQL prompt
* The exact time it takes to query all of PARCELS from a SQL prompt
* The exact time it takes to query the subset of PARCELS which intersect BOUNDARY from a SQL prompt
* The exact time it takes to query the view from a SQL prompt
* The exact time it takes to draw the view in ArcMap.
* The spatial index options used on the PARCELS geometry column index

- V