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.