Home - General / All posts - query optimization
 vincent       1,750 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ère123 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))
 tjhb            8,578 post(s)online #04-Jul-18 19:45 It is much better to use a join, then update the joined table. Example soon.
 vincent       1,750 post(s) #04-Jul-18 20:05 Great ! I'll try that too on my side.
 vincent       1,750 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
 tjhb            8,578 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.
 vincent       1,750 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 !
Manifold User Community Use Agreement Copyright (C) 2007-2017 Manifold Software Limited. All rights reserved.