MSSQL Spatial Query (View) is Slow in ArcMap

Question

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?

---

Update:

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.

Answer 1

If it does not have to be a view, some expensive operations, like this spatial filter, are best handled in ETL, or in a scheduled task in MSSQL.

If it needs to be real-time, then I guess you'll have to put up with some slowness. If you can handle data that is a day old, set up a task to cache the data.