I've got some data in an enterprise geodatabase (SQL Server 2012). I want to create a view that joins points in a feature class to the nearest line in another feature class. The join includes the shape field and attributes from the point feature class, plus some attributes from the nearest line, plus the distance to the nearest line. I wrote a simple but inefficient view that works for a small test dataset, but performance quickly degrades. 6 seconds for 2000 points against 20 lines. Looking at the execution plan, the spatial index is not being used at all. I manually created the spatial index in ArcCatalog for both the points and the lines with coordinates that bound all of the data with Medium grid granularity at all levels.
Here is the code, looking for tips on how to engage the spatial index or better, more scalable logic in general:
--POINTS is the point feature class --LINES is the line feature class select p.OBJECTID, p.SHAPE, p.PointAttribute, closestline.LineAttribute, p.shape.STDistance(closestline.shape) from POINTS as p cross apply ( select top 1 l.LineAttribute, l.Shape from LINES as l order by p.SHAPE.STDistance(l.SHAPE) ) as closestline