I created the following view in Microsoft SQL Server 2008 R2:
CREATE VIEW test AS SELECT p.id, p.shape FROM p INNER JOIN r ON p.shape.STIntersects(r.shape) = 1 WHERE r.Name = 'Test'
The view runs very quickly within MSSQL if the spatial index is enabled on P. The view runs very slowly within MSSQL if I disable the spatial index.
If I add the view to ArcMap 10.3.1 (via Catalog) it is very slow. It doesn't matter if the spatial index is enabled or disabled - the view still runs very slowly within ArcMap.
How can I improve the view's performance within ArcMap?
The SQL actually being called by ArcMap seems to take the following form:
select SHAPE from (select id, SHAPE from test) a where SHAPE.Filter(geometry::STGeomFromWKB(@P1,@P2)) = 1
I think that the WHERE clause translates to SHAPE.Filter(SHAPE) = 1. When I run this query within MSSQL it returns the results but now it's as slow as ArcMap.