Find Nearest Sides (Polygon)

[NeoPa]

I'm just busy at the moment getting timing info for the three states I'm interested in :

Main1 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID]
Main2 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] + Ix4=[SourceUID]&[TargetUID]
Main3 = PK=[ID] + Ix2=[SourceUID] + Ix3=[TargetUID] + Ix4=[SourceUID]&[TargetUID]&[Distance]

When I received the database, the table Nn was set up as (approx) Main1. My expectation is that Main3 will provide the quickest results. I'm using the SQL from post #18 as the query, but saved into a pre-optimised QueryDef for consistency.

I'll post when the results arrive.

[NeoPa]

Well, that'll teach me.

I ran the tests only once, but my results were :

Main1: 01/05/2008 01:54:39 - 01/05/2008 02:02:03 = 7'24"
Main2: 01/05/2008 01:34:21 - 01/05/2008 01:42:32 = 8'11"
Main3: 01/05/2008 01:45:27 - 01/05/2008 01:53:01 = 7'34"

I may have to run them again after fiddling the SQL to return the correct results, but these results certainly indicate that the added index didn't help (as I would have anticipated).

Anyway, enough for tonight. I'm for bed :)

PS. My code was :

?"Main1: " & Now(); : Call DoCmd.OpenQuery("qryTop2",acViewNormal) : ?" - " & Now()

I redesigned the table and changed the value in the first string between each run of course (and calculated the resultant time manually and added it afterwards).

[NeoPa]

My curiosity got the better of me so I tried running with only the ID index set, and using the following (more refined) code :

datBeg=Now() : _
  ?"Main0: " & datBeg; : _
  Call DoCmd.OpenQuery("qryTop2",acViewNormal) : _
  datEnd=Now() : _
  ?" - " & datEnd & " = " & Format(datEnd-datBeg,"h\'n\""")

I gave up after over 20 mins had elapsed with the output still saying :

Main0: 01/05/2008 02:23:33

:(

[NeoPa]

I produced some more SQL to handle this situation. It is a considerably more complicated situation than the previous one I'm afraid, and you can expect quite severely slowed performance.
[CODE=SQL]SELECT SourceUID,
TargetUID,
Distance
FROM (SELECT SourceUID,
TargetUID,
Distance,
(SELECT Count(*)
FROM (SELECT SourceUID,
TargetUID,
Min(Nn.Distance ) As Distance
FROM Nn
GROUP BY SourceUID,
TargetUID) AS iNn
WHERE iNn.SourceUID=o Nn.SourceUID
AND iNn.Distance<oN n.Distance) AS Pos
FROM Nn AS oNn) AS sNn
WHERE Pos<2
ORDER BY SourceUID,
Pos[/CODE]
I'm time-testing now but as I post it's taken over 20' :(

[NeoPa]

I set up the time trial with my best indexing and it still took over 1:30 (when I aborted).

I will have to think of a practicable way to handle this. It will almost certainly involve adding an ordinal field to the table and an update query being run first.

Disappointed -NeoPa.

[NeoPa]

As a last attempt before looking at a table update method, I've noticed that both SourceUID & TargetUID are text fields of 255 allowable characters. None of the values is longer than 7 characters so I will see what setting the field lengths to ten does. I also changed the index I created (SourceUID; TargetUID; Distance) to specify Unique.

I understand that the space taken up in the data is no more if the field is specified as 255 than if it's done as ten, but it may effect the indexing performance. I don't know. I'll see.

[NeoPa]

2.5 hours later there are still no results.

Regardless of whether or not this is more efficient, it's clearly not a solution that is workable in the real world.

Next:
Add a field into the table which is reset for every run. Essentially a scratch field which reflects the ordinal position of the Point/Side record by Distance. Maybe even a simple flag to indicate whether or not it's the closest.

That way a SELECT query can be run along similar lines to the earlier versions, but which ignores all but the nearest Distances from the start. A much more manageable set of records to deal with. I'll post progress.

[NeoPa]

Step 1:

The procedure (that works) is first of all to add a new Yes/No field called [Nearest] to the table. This is not added into any indexes. The recently added Main index (SourceUID; TargetUID; Distance) should still be in place.

The overall work is done by three queries (QueryDefs in Access). Remember, the optimisation of the QueryDef is done the first time it's run, so don't rely on timings of that run. Run normally, these QueryDefs in sequence, take very little more than 8 minutes in total. Considering the number of records that are to be processed this is not a long time at all.

[NeoPa]

Step 2:

[Nearest] must start off as set (to True) in all records. To do this we have a QueryDef called qryTopSet set up with the following SQL :
[CODE=SQL]UPDATE Nn
SET [Nearest]=True[/CODE]
I used the following code in the Immediate pane to run this and log the time taken :

Call DoCmd.SetWarnings(False) : _
  datBeg=Now() : _
  ?"Main1: " & datBeg; : _
  Call DoCmd.OpenQuery("qryTopSet",acViewNormal) : _
  datEnd=Now() : _
  Call DoCmd.SetWarnings(True) : _
  ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""")

The results of this step were :

Main1: 03/05/2008 02:01:21 - 03/05/2008 02:01:28 = 0'7"

[NeoPa]

Step 3:

Next, the Point/Side records which have [Distance]s which are larger than ANY of the other matching Point/Side [Distance]s, must have [Nearest] reset (to False). To do this we have a QueryDef called qryTopReset set up with the following SQL :
[CODE=SQL]UPDATE Nn AS Nn1 INNER JOIN Nn AS Nn2
ON (Nn1.TargetUID= Nn2.TargetUID)
AND (Nn1.SourceUID= Nn2.SourceUID)
SET Nn1.Nearest=Fal se
WHERE (Nn1.Nearest)
AND (Nn1.Distance>N n2.Distance)[/CODE]
I used the following code in the Immediate pane to run this and log the time taken :

Call DoCmd.SetWarnings(False) : _
  datBeg=Now() : _
  ?"Main2: " & datBeg; : _
  Call DoCmd.OpenQuery("qryTopReset",acViewNormal) : _
  datEnd=Now() : _
  Call DoCmd.SetWarnings(True) : _
  ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""")

The results of this step were :

Main2: 03/05/2008 02:02:15 - 03/05/2008 02:10:11 = 7'56"

[NeoPa]

Step 4:

Now all the records are prepared. Only the nearest Point/Side records are flagged as [Nearest]. Now, and only now, are we in a position to execute the logic (code) that was originally considered for this problem in the knowledge that we're only processing through 1,152 records rather than 161,880. To do this we have a QueryDef called qryTop2 set up with the following SQL :
[CODE=SQL]SELECT sNn.SourceUID,
sNn.TargetUID,
sNn.Distance
FROM (SELECT SourceUID,
TargetUID,
Distance,
(SELECT Count(*)
FROM Nn AS iNn
WHERE iNn.Nearest
AND iNn.SourceUID=o Nn.SourceUID
AND iNn.Distance<oN n.Distance) AS Pos
FROM Nn AS oNn
WHERE oNn.Nearest) AS sNn
WHERE (sNn.Pos<2)
ORDER BY sNn.SourceUID,
sNn.Pos[/CODE]
I used the following code in the Immediate pane to run this and log the time taken :

Call DoCmd.SetWarnings(False) : _
  datBeg=Now() : _
  ?"Main3: " & datBeg; : _
  Call DoCmd.OpenQuery("qryTop2",acViewNormal) : _
  datEnd=Now() : _
  Call DoCmd.SetWarnings(True) : _
  ?" - " & datEnd & " = " & Format(datEnd-datBeg,"n\'s\""")

Main3: 03/05/2008 02:10:50 - 03/05/2008 02:10:57 = 0'7"

The results are a perfect set of 190 records (95 pairs) exactly as required :)

[NeoPa]

I'm curious to know how you got on with this. I received your e-mail, but I don't know if 8 minutes was within the sort of time-frame you'd envisaged as acceptable.