1

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.

  • In each case, how are you consuming the view? – PolyGeo Jul 6 '16 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 '16 at 11:14
  • Similar unanswered question here: gis.stackexchange.com/questions/75615/… – Dion Jul 11 '16 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 '16 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/… – Kirk Kuykendall Jul 13 '16 at 21:00
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 '16 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.