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


What you have there is a Correlated Subquery. The results of the inner (SELECT '; '+ b.[ZONE_CODE] FROM [TableB] etc ... will vary depending on a value (the geometry) from TableA. That means the SELECT must be executed 3.5 million times - once for each of the rows in TableA.

I've rewritten the query using CTEs (common table expressions) in the hope it might create some caching/reuse of the table joins:

with step1  as 
SELECT  A.id, B.Zone_Code, B.id as Bid
FROM  TableA AS A 
 ON A.geometry.STIntersects(B.geometry.MakeValid()) = 1                  
step2 as 
    (SELECT N'; ' + Zone_Code FROM step1
       WHERE id = s.id
       ORDER BY Zone_Code
       FOR XML PATH, TYPE).value(N'.[1]',N'nvarchar(max)'),1,1,'') as zones
       from step1 as s
group by id

SET Zone_Codes = step2.zones
FROM TableA INNER JOIN step2 on TableA.id=step2.id 

But I don't have a huge spatial table to test it on so I'm not sure if it will help or hinder.

| improve this answer | |
  • Gave this a go with a subset of my data and haven't noticed a significant increase in processing time unfortunately. its possible the area and dataset is just too large to see any real gains in terms of performance – Jay Edwards Aug 27 '19 at 22:17
  • Thanks for trying Jay. If I was trying to speed things up the next thing I would do is test the individual steps of the query to see if the slow down is in the initial STIntersects or in step2, which still has a correlated subquery, but I hoped the CTEs might help. I don't know how costly the MakeValid() is but I'm guessing you found issues when not using it. I'd even try rearranging the intersects to B.geometry.MakeValid().STIntersects(A.geometry) = 1 on the chance that having the TableB with the fewer records on the outside might help. Lastly are temp tables an option? – M Bain Aug 27 '19 at 23:02
  • MakeValid() isnt having any effect on runtime what so ever. if it was id just run the update on the dataset before running this query. Temp tables are always an options. At the moment i am trying to run it through a loop by splitting up the dataset into smaller grids to reduce the number of comparisons in each area. currently going on 20hrs with running but some quick testing seemed to indicate it was more efficient going this direction. ill post the code – Jay Edwards Aug 28 '19 at 5:39
  • 20hrs! Wow, keen to see what you come up with. I should maybe delete my answer as it's really just noise. Good luck. – M Bain Aug 28 '19 at 5:49
  • i amended the question with latest version of code. let me know what you think – Jay Edwards Aug 28 '19 at 5:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.