Re: Tuning SQL

"Barry Jones" <jonesb_at_logica.com> wrote:
> for best possible performance, assuming your max radius is 20, avoid the
> time consuming SQRT by testing
>
> ( POWER(x2-x1,2) + POWER(y2-y1,2)) < 400.
>
> If this solution is unsuitable you could try defining your own
> approximating functions for sin, cos etc, but using less precision in the
> infinite series or you could define a simple sin / cos etc table which you
> use as a lookup table instead of calling oracle math functions.

A standard optimization for things like this goes several steps further.

If either x2-x1 or y2-y1 is greater than 20, then the pythagorian distance must be greater than 20. So, you end up with something along the lines of:

/* pseudocode */
function inside (x1, x2, y1, y2, r)
{

   dx = x2-x1
   if (abs(dx) > r)

      return false

   dy = y2-y1
   if (abs(dy) > r)

      return false

   if ((dx*dx + dy*dy) > r*r)

      return false

   return true
}

If the points are reasonably evenly distributed over an area significantly larger than r, then the first linear comparison will reject most of the points, the second linear comparison will reject most of what's left, and you'll only have to slog through the more complex math for the small number which remain. This becomes even more significant when dealing with great-circle distances in non-uniform lat/long coordinates, since the math is uglier, as you specified:

> v_distance := 5499.0*acos(sin(v_radA1)*sin(v_radA2) +
> cos(v_radA1)*cos(v_radA2)*cos((p_longitude1/180*3.14285) -
> (p_longitude2/180*3.1428)))

You certainly don't want to do all those trig functions if you can possibly avoid it. Come to think of it, you may have to modify the first two linear comparisons a bit to work right with lat/long. Maybe for a given x1, x2, you might work out that you need to be within the 25-minutes-of-longitude x 15-minutes-of-latitude box, but after that it's the same idea.

--
Roy Smith <roy_at_popmail.med.nyu.edu>
New York University School of Medicine Received on Fri May 01 1998 - 14:46:48 CDT