AnsweredAssumed Answered

Optimizing STIntersects queries: Versioned Data in a Database View

Question asked by TempletonDemographics on Jul 1, 2016
Latest reply on Jul 5, 2016 by TempletonDemographics

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

AS

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.

 

Any suggestions?

 

Using:

ArcGIS for Desktop 10.4

ArcGIS for Server 10.4

Microsoft SQL Server 2012

Outcomes