Subscribe to this thread
Home - General / All posts - query optimization
vincent

1,972 post(s)
#04-Jul-18 16:49

Hi,

I'm working in M8. I have the following query :

--SQL

Update [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ère
153 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
10,094 post(s)
#04-Jul-18 19:45

It is much better to use a join, then update the joined table. Example soon.

vincent

1,972 post(s)
#04-Jul-18 20:05

Great ! I'll try that too on my side.

vincent

1,972 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
10,094 post(s)
#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,972 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-2021 Manifold Software Limited. All rights reserved.