Tuning SQL

From: Andrew <andy_at_webworks.ca>
Date: Fri, 01 May 1998 13:21:54 GMT
Message-ID: <3549CD74.C886842A@webworks.ca>

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 Received on Fri May 01 1998 - 08:21:54 CDT