Hi Everyone, I've got a question that is similar to one that has already been answered here: Optimizing STIntersects queries
That thread concludes that you take a massive performance hit if you try to use STIntersects directly on a versioned view in SQL Server Mgmt Studio because it can't use the spatial index of the versioned view, so it's best to use temporary tables to circumvent the problem. They also threw out a possible solution of manually applying your query to the A tables, but there wasn't an example.
My trouble is that I am trying to do the same thing, but use my select statement to create a database view. Here is a simplified example:
CREATE VIEW dbo.polygonwithpointtotal
SELECT MIN(poly.OBJECTID), MIN(poly.NAME), SUM(pts.TOTAL),
geometry::UnionAggregate(poly.SHAPE) AS SHAPE
FROM dbo.polygon_evw as poly
LEFT JOIN (SELECT * FROM dbo.points_evw WITH(INDEX(S33_idx)) WHERE ptsid = 1) AS pts
ON poly.SHAPE.STIntersects(pts.SHAPE) = 1
GROUP BY poly.OBJECTID
And the hang-up is with the STIntersects method. If I run it with the default tables (without the _evw at the end) the query takes about 1 second - a perfectly reasonable load time when working with a view in ArcMap. But when I use the versioned view it takes about 30 seconds and the execution plan makes no mention of utilizing a spatial view even though I'm trying to force it in the query.
ArcGIS for Desktop 10.4
ArcGIS for Server 10.4
Microsoft SQL Server 2012