Re: Tuning SQL

Andrew wrote:

> Hi Folks.
>
> I am currently doing a cross referencing bit in oracle where I
> have a postal code and obtaining the latitude and longitude from out
> Postal Code database of 750000 records. Then I am cross referencing a
> set of criteria with a list of people of 32000 records. Now if I don't
> bother with a radius calculation to narrow down the search (which
> incidenty turns up about 10000 matches) the query comes back in about 7
> seconds. However, if I include the radius calculation (ie. return the
> people that fall in a 20 km radius).. the query takes 90 seconds... This
> is too long. Oracle is running on a fairly strong Solaris box (166 Mhz
> 128 MB Ram) barely tasked at all
>
> I am wondering if anyone has some advise about performing the
> distance calculation within PL/SQL and its obvious degradation in
> performance.
>
> My block looks something like:
>
> CURSOR ranks_list (c_lat,c_long)
> (c_provinceid personinfo.provinceid%TYPE) IS
> SELECT personinfo.id, firstname, lastname, cityname,
> provinces.abbrev, latitude, longitude,
> FROM postalcodes, personinfo, provinces
> WHERE provinces.id = provinceid
> AND personinfo.provinceid = c_provinceid
> AND personinfo.postalcode = postalcodes.postalcode
> AND getDistance(latitude,longitude,c_lat, c_long) <=
> radius;
>
> Where getDistance takes in each of the lat and long for all of the
> matches and elimiates ones that are not within the radius. Now if I
> remove the [AND getDistance(latitude,longitude,c_lat, c_long) <=
> radius], this thing returns the list in 7 seconds... with it left in, it
> takes 90 seconds...
>
> The function is as follows: ( we already downgraded the calcs from the
> curve distance calcs to this one which is the linear calc)
>
> FUNCTION getDistance2 (
> p_latitude1 PostalCodes.Latitude%TYPE,
> p_longitude1 PostalCodes.Longitude%TYPE,
> p_latitude2 PostalCodes.Latitude%TYPE,
> p_longitude2 PostalCodes.Longitude%TYPE)
> RETURN NUMBER
> IS
> v_distance NUMBER(20,2);
> v_radA1 NUMBER(20,6);
> v_radA2 NUMBER(20,6);
> BEGIN
> v_radA1 := p_latitude1/180*3.14285;
> v_radA2 := p_latitude2/180*3.14285;
> 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)));
> RETURN v_distance;
> END getDistance2;
>
> Any insight would be appreciated.
>
> Thanks
> Andrew

1. calculate stuff like 180*3.14285 once and use the result in the code (add a comment)
2. try not to use a function and write the calculation directly in the statement (PL/SQL is slow)
3. precalculate sin/cos(p_latitude1/180*3.14285) etc. once and store it in 2 additional columns and don't forget to maintain them (use a UPDATE/INSERT trigger for that)