Re: Tuning SQL

Hi Andrew,

The use of the function will stop Oracle using any index to identify the records you want based on the radius. Another approach may be to use the function to return a value and then to use this value as part of the query criteria, i.e. do it in two steps.

e.g.

c_distance := getDistance(latitude,longitude,c_lat, c_long);

CURSOR ranks_list (c_lat,c_long,c_distance)

                  (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 c_distance <= radius;

This won't decrease the processing time of getDistance, but assuming radius is indexed it should make the query faster.

Hope this helps.

Cheers,

Allan
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

Received on Sun May 03 1998 - 06:57:45 CDT