Subscribe to this thread
Home - General / All posts - M8 versus M9 : action query
yves61
192 post(s)
#09-Nov-20 22:09

I am comparing query execution between M8 and M9 on a project.

I have 4 drawings,

Gbg20001 totals 793982 records 'buildings ; all area type

Gebrprc totals 79465' parcels ; all area type

Gwptotals38758' Planning ; use all area type

ZR_KMO_VLBRtotals 65 records' Selection area ; all area type

all drawings have the same projection

all tables have the necessary indexes :

With M8 : the built in auto index on 'id' field

With M9 : btree on 'mfd_id' and rtree on 'geom' field

***

The query is aiming at finding the unbuilt parcels , subject to some criteria

with M8 - I am running this action query

****************************************

SELECT * into [Unbuild]

FROM (

SELECT gwp.[hoofdcode], gwp.[grondkleur], subQ.*

FROM (

SELECT

P1.id,

P1.oppervl,

P1.[X (I)],

P1.[Y (I)],

P1.[Geom (I)]

FROM

[Gebrprc] as P1 ,

[ZR_KMO_VLBR] as Z1

WHERE

P1.oppervl >= 5000

AND

CONTAINS (Z1.[Geom (I)],P1.[Geom (I)])

---

EXCEPT

(

SELECT

P1.id,

P1.oppervl,

P1.[X (I)],

P1.[Y (I)],

P1.[Geom (I)]

FROM

[Gebrprc] as P1,

[Gbg20001] as G1

WHERE

P1.oppervl >= 5000

AND

CONTAINS (P1.[Geom (I)],G1.[Geom (I)])

)

---

EXCEPT

(

P1.id,

P1.oppervl,

P1.[X (I)],

P1.[Y (I)],

P1.[Geom (I)]

FROM

[Gebrprc] as P1,

[Gbg20001] as G1

WHERE

P1.oppervl >= 5000

AND

INTERSECTS (P1.[Geom (I)],G1.[Geom (I)])

)

) as subQ , [Gwp] as gwp

WHERE

TOUCHES(gwp.[Geom (I)],subQ.[Geom (I)])

)

*******************************

*

*

*

WITH M9 - I am using this similar query

**********************************

SELECT * INTO [Unbuild]

FROM (

SELECT gwp.[hoofdcode],gwp.[grondkleur], subQ.*

FROM (

SELECT

P1.[mfd_id],

P1.[OPPERVL],

VectorValue(GeomCenter(P1.[Geom],0),0) as XL72,

VectorValue(GeomCenter(P1.[Geom],0),0) as YL72,

P1.[Geom]

FROM

[Gebrprc] as P1,

[ZR_KMO_VLBR] as Z1

WHERE

P1.[OPPERVL] >= 5000

and Geomcontains(Z1.[Geom (I)], P1.[Geom],0)

----

EXCEPT

SELECT

P1.[mfd_id],

P1.[OPPERVL],

VectorValue(GeomCenter(P1.[Geom],0),0) as XL72,

VectorValue(GeomCenter(P1.[Geom],0),0) as YL72,

P1.[Geom]

FROM

[Gebrprc] as P1,

[Gbg20001] as G1

WHERE

P1.[OPPERVL] >= 5000

AND

Geomcontains(P1.[Geom], G1.[Geom],0)

----

EXCEPT

SELECT

P1.[mfd_id],

P1.[OPPERVL],

VectorValue(GeomCenter(P1.[Geom],0),0) as XL72,

VectorValue(GeomCenter(P1.[Geom],0),0) as YL72,

P1.[Geom]

FROM

[Gebrprc] as P1,

[Gbg20001] as G1

WHERE

P1.[OPPERVL] >= 5000

AND

GeomIntersects(P1.[Geom], G1.[Geom],0)

) as subQ , [Gwp] as gwp

WHERE

GeomTouches(gwp.[Geom], subQ.[Geom],0)

)

**********************************

*

*

Conclusion:

The M8 query runs fine (on my laptop : 1 min 3 seconds)

The M9 query runs but ....

a) much slower (2 min 12 seconds)

b) the query completes; it creates the 'Unbuild' table , adds 1 record then throws an error : cannot add record

Looking at the schema of the 'Unbuild' table I see no right geometry is created on the 'geom' field

*

*

Any ideas why a) M9 is running slower ?

Any ideas why b) M9 throws an error ? and stops adding records

yves61
192 post(s)
#09-Nov-20 22:24

In the mean time I see that if I leave out the P1.mfd_id field from the M9 query , the query completes , no error is thrown, and all records are added.

I see I can add a Btree index on a Mfd_id field to create, and add a Rtree index on the 'geom' field.

Still - - the M9 query takes twice the time to complete as the M8 query.

Any ideas to improve the speed of the M9 query ?

adamw


9,445 post(s)
online
#10-Nov-20 06:59

The query for 9 was not completing with MFD_ID in the SELECT lists because it was trying to insert multiple records with the same value of that field. This is not allowed, the MFD_ID field in a table from a MAP file has to be unique. You removed the field from the SELECT lists, one other solution would be to rename it (eg, to just 'ID').

The performance is hard to assess without example data - could you post some? In general, some computations happen in the inner part: SELECT ... geomcontains(z1, p1) EXCEPT SELECT ... geomcontains(p1, g1) EXCEPT SELECT ... geomintersects(p1, g1) --- and some in the outer part: SELECT ... geomtouches(gwp, part1). The second part should use an RTREE index on gwp (if you post example data, I'll check whether it does this). The part that should be improved is likely the first one. A natural way to improve the first part would be to switch geometry calls to GeomOverlayXxx. The filtering on OPPERLVL would be done on the result of GeomOverlayXxx. Once the query is working, you might add threads by switching to GeomOverlayXxxPar. What the effect on the performance is going to be depends on the data.

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.