Re: performance issue with distance function

Ryan Littrell wrote:
> 
> I am trying to execute the following command:
> 
> SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance
> FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR
> WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND
> R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance(L1.lat,
> L1.lon, L2.lat, L2.lon) <= 60
> LIMIT 100  OFFSET 0
> 
> I would rather execute this command: (but i get the error "Attribute
> 'distance' not found")
> 
> SELECT R.*, distance(L1.lat, L1.lon, L2.lat, L2.lon) AS Distance
> FROM Restaurants R, Locations L1, Locations L2, FoodTypeRestaurantIDX FTR
> WHERE R.Zipcode=L1.Zipcode AND L2.Zipcode = '93705' AND R.Delivery=true AND
> R.RestaurantID=FTR.RestaurantID AND FTR.FoodTypeID=1 AND distance <= 60
> LIMIT 100  OFFSET 0
> 
> Having that second distance function in the "WHERE" section of my sql
> statement is costing me at least 10-20 seconds of execution time.  I am
> looking for a solution that will speed this up. Does anyone have any advice.
> Thanks in advance.
> 

this probably isn't what you want, but would it speed things up if you
did an "order by distance" instead of doing the "distance <= 60", then
having your application cut the results at 60?  that should work from a
language perspective, at least, and if the distance function is pretty
computationally intensive, it should help.
-- 

Jeff Hoffmann
PropertyKey.com