I created the following view in Microsoft SQL Server 2008 R2:

    p INNER JOIN r ON p.shape.STIntersects(r.shape) = 1 
    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 id, SHAPE from test) a 
    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.

  • In each case, how are you consuming the view?
    – PolyGeo
    Jul 6, 2016 at 6:29
  • 1
    ArcMap always applies a spatial query filter. SQL-Server is probably doing the spatial join before the WHERE clause. You can try using a hint to untangle Microsoft's optimizer. In the future, please include exact time(s) (in seconds or milliseconds) and query plan(s) with all database performance questions.
    – Vince
    Jul 6, 2016 at 11:14
  • Similar unanswered question here: gis.stackexchange.com/questions/75615/…
    – Dion
    Jul 11, 2016 at 5:28
  • Have you tried something like select id, shape from p where exists (select * from r where p.shape.STIntersects(r.shape) = 1).
    – MickyT
    Jul 12, 2016 at 3:28
  • Is it any faster if you add a query layer using the same inner join logic? desktop.arcgis.com/en/arcmap/10.3/map/working-with-layers/… Jul 13, 2016 at 21:00

1 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.

  • At the moment I am making do with a scheduled task that creates a spatial table from the view once a day. This is far from ideal. The spatial query is simple and MSSQL has no problems with it. ArcMap is adding the filter and slowing things down. It might be the case that useable realtime spatial queries aren't possible with ArcMap and MSSQL?
    – Dion
    Jul 15, 2016 at 10:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.