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

Question

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.

EvanD · Answer

I installed WireShark but it is well outside my area of expertise. I'm guessing I need to set up a capture filter and then try to run the sql in ArcMap, but that's as far as I got.

I'll try some importing/exporting.

mvolz47 · Answer

Have you tried to run software such as WireShark to audit the calls being made to the SQL Server database from ArcMap?  In my experience with query layers some of the calls are being incorrectly created by ArcMap when being sent to the database (e.g. where clause being dropped so all records are being retrieved when in reality only a subset should be retrieved).  Might not be happening to you, but WireShark is useful to trace this specific type of activity when troubleshooting.

Also try exporting data from the SQL Server database to a file geodatabase to create ESRI recognized spatial feature classes and see if the same problem occurs.  You could also export the data to an SDE SQL Server database to see if you get better results with standard GIS layers and not query layers.