Subscribe to this thread
Home - General / All posts - Selecting Duplicates
joebocop
348 post(s)
#08-Oct-18 15:40

I have a local table with ~60k records in a Release 9 map file. Each record has a non-computed Geom field representing a point.

The table has an mfd_id field with a btree index on it.

The table has another field, "sample_id" which has a btreedupnull index on it. The Geom field also has an index of type rtree on it.

I am trying to "Select" (using the Select pane; not just "return via sql") only those points having "duplicate" sample_id.

With the Drawing component (based on the table's geom field) open, I open the "Expresion" tab on the "Select" pane, and paste-in the code below.

sample_id IN (SELECT sample_id

FROM [LAB-Export]

where sample_id IS NOT NULL

GROUP BY sample_id

having count(*)>1)

The progress dialog that opens indicates that records are being scanned at a rate of ~25/s which is too slow for this workflow to be usable. For reference, the subquery using GROUP BY returns only a single record in this case, so it's not as if there is some cartesian product here that is too large to be processed.

I had a forum posting similar to this previously; I am struggling to "select" subsets efficiently in 9, and am motivated to improve my workflows. Thanks for the continued pointers on refining my techniques in 9.

adamw


8,259 post(s)
#09-Oct-18 10:55

We need to add a template to select duplicates.

In the meantime, you can improve the performance by creating a temporary table with all duplicate IDs, adding a (BTREE) index on the subject field to it, and referencing it in the expression. Eg, instead of doing [sample_id] IN (SELECT ...), first run SELECT ... in a regular command window adding INTO to put the result into a regular table, then make sure the produced table has an index on its only field, then use an expression of [sample_id] IN [temp_table].

danb


1,668 post(s)
#29-Oct-18 19:59

We need to add a template to select duplicates.

Yes please. I also find 'Duplicates except first' in M8 invaluable.


Landsystems Ltd ... Know your land | www.landsystems.co.nz

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