A customer of ours (an electric utility company) wants to increase the use of the grid data registered in ArcGIS. Recently their BI department has asked us to help in establishing various reports / queries from outside ArcGIS.
An example of such query is to list substations per maintenance area / sub area. This query delivers the requested list:
From ELDB.EL.BYGVAERK_EVW byg,
Where Byg.BygvaerkArtKode IN (1,2) AND
Byg.SHAPE.STIntersects(eft.SHAPE) = 1
Order by eft.Betegnelse,
(BYGVAERK_EVW is the versioned view of substations and Eftersynsomraader_evw is the versioned view of maintenance areas).
Executing this query in MS SQL Server Management Studio takes 3 minutes.
If in ArcMap I do a spatial join between the two feature classes I am able to present the result in approximately 1½ minutes. If ArcMap is able to deliver the requested answer in 1½ minutes I don't see why asking for the same trough non-ArcGIS queries shouldn't be able to also deliver the answer in 1½ minutes - or even faster.
I have a feeling that the answer to optimizing my query lays in the use of spatial indexes. But how should I approach implementing this?
Any help would be appreciated.