Home - General / All posts - query optimization
 vincent1,717 post(s) #04-Jul-18 16:49 Hi, I'm working in M8. I have the following query : --SQLUpdate [Points_isol] as [D1] Set [slope_20m] = ( ((Select [D2].[elev] from [Points_isol] as [D2] where  [D2].[ID] = ([D1].[ID] + 20) )-[D1].[elev]) / 20 * 100) The purpose is to calculate the slope between each point in the drawing and the point that is 20m farther (which is ID+20 in the ID field). The [elev] field contains the altitude. The query is working nicely, but if it can runs faster, that would great !Running the WHERE clause is what is time consuming. Any ideas ?Thank you.
 LeRepère121 post(s) #04-Jul-18 19:19 Voici ma suggestion.Update [Points_isol] as [D1] Set [slope_20m] = (Select (([D2].[elev]-[D1].[elev]) / 20 * 100)from [Points_isol] as [D2]where [D2].[ID] = ([D1].[ID] + 20))
 tjhb8,227 post(s)online #04-Jul-18 19:45 It is much better to use a join, then update the joined table. Example soon.
 vincent1,717 post(s) #04-Jul-18 20:05 Great ! I'll try that too on my side.
 vincent1,717 post(s) #04-Jul-18 21:15 No luck Gérald. Pas une seconde de gain. ***Still have to make sure the results are good with the Tim's idea, but it is really faster : Update ( SELECT [A].[slope_20m], [A].[ID] , [A].[elev], [B].[elev]from [Points_isol] As [A]  FULL JOIN [Points_isol] As [B] ON [A].[ID] = ([B].[ID] + 20) )SET [slope_20m] = ([A].[elev] - [B].[elev]) / 20 * 100
 tjhb8,227 post(s)online #04-Jul-18 23:23 That's exactly it. The difference is that for the earlier query, the match in the correlated subquery is (re)evaluated once for each row in [Points_isol], i.e. for each SET operation; while for the version using a join, all matching is done once in advance, then all SET operations are quickly applied to the result.
 vincent1,717 post(s) #05-Jul-18 13:34 The whole script is running 13 times faster now. That was the last slow part. Thank you for all the tips !