fastest way to search in database

[Erel]

I've compressed one of the images and uploaded your project.

[agraham]

I assume that it is sub WPcheck that you want to speed up. A couple of suggestions then :-

Xdistance = WP.Cell("X",i) - utmi(1) 
If abs(Xdistance) < appr Then       
    ' carry on and check Y
End if

The single comparison is neater and faster then the code below. Also saving the distance is faster as it minimises variable accesses and computation time and you would need that result again for its' sign for a binary search (see below).

If WP.Cell("X",i) > utmi(1)-appr Then        '200m voor appr= 200
    If WP.Cell("X",i) < utmi(1)+appr Then    '200m na
        ....
    End if 
End if

If you sort the table by X (or Y)and do a binary search, rather than a linear one like you are doing at the moment, to pick out the possible X (or Y) matches and then do the other comparisons you should end up with a dramatic increase in speed on a large table. Your 1500 item table would only need about 12 of my simplified comparisons above to isolate a WayPoint or decide that you were not near enough to any of them.

[LineCutter]

You can cut that further by knowing the region of interest in the data so that the binary search starts inside the absolute boundaries of the list.

For the sake of argument, take the position of the first point found (search the whole database) & then use that index +&-10% of the size of the database as the limits for the next search. Remember, with GPS data at 1 second intervals there are going to be no nasty surprises jumping from one end of the database to the other.

Fewer data points to search == quicker.

[Put Claude]

hi agraham, LineCutter,

@ agraham,
did the binair search, and it was a big improvement, only 200 mSec maximum in 11 jumps, In place of 13 Seconds. Now I go try Your code, like you say, it seams that I lost much time going trough approch distance calculations every comparasion. Thanks very much...
Tested your code, great improvement, for 1500 point it speedup from 31 msec to 15 mSec...
@ LineCutter,
After code improvement, I see that your suggestion simply is a must, without going that way it is not possible to get result, so making regions at start with less waypoints to check, I understand, I will be busy for a while now.... Seams you are well now with the problem... and thank you also for pulling me in the right direction..

After reading carfully, I see I misunderstud : I understand now, I have to change the startpoint to search from in the waypointlist from 8 to 10 % around the actual position point every fetch of the new position... so I must only compare a few points in the enviremont.

Put claude Belgium

[LineCutter]

Originally Posted by Put Claude

After reading carfully, I see I misunderstud : I understand now, I have to change the startpoint to search from in the waypointlist from 8 to 10 % around the actual position point every fetch of the new position... so I must only compare a few points in the enviremont.

You might want to make that +/- 100 points or 10%, whichever is greater. (Or any other numbers, the choice of 100points/10% was arbitrary)

That way you won't miss nearby waypoints in a small database.