I'm sure similar answers have been given elsewhere but no matter what I have tried I am unable to optimise this query.
Details: TableA has 3.5mill records TableB Had 57000 records but I have dissolved that down to 170 based off category of data.
I need to return what features in tableB intersect each individual record in tableA in a single string.
Update a Set a.[Zone_Codes] = (SELECT SUBSTRING((SELECT '; '+ b.[ZONE_CODE] FROM [TableB] AS b WHERE a.geometry.STIntersects(b.geometry.MakeValid()) = 1 ORDER BY b.[ZONE_CODE] FOR XML PATH ('')), 2, 1000)) FROM [TableA] AS a
Any thoughts on how to optimize this? All tables have spatial indexes. SQL Management Studio 13
--- removed amended code, didnt optimise