AnsweredAssumed Answered

How to perform a spatial select against a Sql Server query layer (keeps crashing)

Question asked by haresfur on Aug 15, 2019
Latest reply on Aug 18, 2019 by haresfur

I'm having trouble with ArcMap 10.4.1 hanging when performing select by location. I have point data stored in a sql server database table (Bores) with a geometry field, GeogLoc. I can do a select by location from a query layer based on the Bores table to get points within a polygon in a layer, Basin100, from a geodatabase. However, when I join the Bores table to my sql server WaterLevel table with a one-to-many PK to FK relationship, and then try to select the locations, ArcMap hangs.

 

I have a clustered index on the PK, BoreID in the Bores table and a non-clustered index of the FK in the WaterLevel table. I have tried a spatial index on the GeogLoc field in the Bores table. I have tried both creating the join in the sql for the query layer and by creating a view in sql server. I tried indexing the view to make it a materialized view. None of that helps. It appears everything on the sql server side works properly and quickly.

 

I have tried modifying the sql for the query layer so that fewer than 10 bore locations are returned and then the select by location is successful, but very slow. If I increase it to ~100 locations (< ~1000 records), the operation hangs.

 

I would appreciate any advice on why this is not working and possible solutions or work-arounds.

Outcomes