Optimizing STIntersects queries: Versioned Data in a Database View

[ForrestChevaillier1]

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

[JoshuaBixby]

You speak of load times in ArcMap.  Are you just testing this in ArcMap or is the final venue/platform for this ArcMap?  If the latter, I have found performance and functional issues using versioned views to access versioned data with ArcGIS products instead of using the regular methods for accessing versioned data.

[ForrestChevaillier1]

The final venue is for ArcMap, I want this to be a layer accessible by my users. I am testing the speed by running just the SELECT query in SSMS, then I will go ahead and create the view and test it in ArcMap.

-ArcMap loads quickly when I am using the default tables to create the view and SSMS runs the SELECT query in less than a second.

-ArcMap loads slowly when I am using the versioned view to create the view and SSMS runs the SELECT query in 29 seconds.

What are the regular methods of accessing versioned data? I am new to SQL Server and versioning.

[ChristianWells]

Chloe Thomas​ when running the query without "_evw" you wouldn't be looking at the default tables. You would be looking at only a single table, referred to as the "base" table. This table contains only the rows that have been fully reconciled with the DEFAULT version and compressed. Because you are only looking at one table it is much faster than the versioned view, which queries over 5 different tables (Base, Adds, Deletes, States, State_Lineage).

In addition, I would avoid using the versioned_view in any ArcGIS application, because it will only ever point to the DEFAULT version of the data. Instead, it is better to just use a copy of the feature class and specify the version that you want to use. This is because the queries that ArcGIS sends to a versioned feature class are much different than the versioned view, including the execution plan.

Two options I would explore are:

1. Use the query in SSMS to create a stand-alone table with a spatial index; regenerating as needed

2. Perform the analysis of aggregation and spatial analysis in ArcGIS instead of SQL

[ForrestChevaillier1]

Thank you, very useful stuff. I will try out both options in the next few days and let you know what I end up doing.

For option 1, would this entail setting a trigger that re-creates the table every time there is an edit to either the polygons or the points, or did you have another method in mind?

For option 2, could I accomplish this with a Query Layer?