CODEWITHSUNDEEP
sqlsql-server
Oliver
Oliver

Reputation: 11617

Simple functions in queries

I need to return all the entries in my database that are fairly close to a given point. At the moment I am doing this (in the WHERE section):

OR (    
    (Addresses.Latitude IS NOT NULL)
    AND (Addresses.Longitude IS NOT NULL)
    AND ( Abs(Addresses.Latitude - @SearchInLat)  < 0.5)
    AND ( Abs(Addresses.Longitude - @SearchInLng) < 0.5)
)

I am doing manhattan distance rather than cartesian distance as my database has a large number of records, and calculating square roots is slow.

I'm pretty new to SQL databases, but I remember reading that it is considered very bad for efficiency to have functions inside SELECT procudures. Does this even apply for really simple functions such as taking the absolute value? Is something like:

OR (    
    (Addresses.Latitude IS NOT NULL)
    AND (Addresses.Longitude IS NOT NULL)
    AND ( (Addresses.Latitude - @SearchInLat)  < 0.5)
    AND ( (Addresses.Latitude - @SearchInLat)  > -0.5)
    AND ( (Addresses.Longitude - @SearchInLng) < 0.5)
    AND ( (Addresses.Longitude - @SearchInLng) > -0.5)
)

a better solution?

Upvotes: 0

Views: 116

Answers (2)

Brian Knight
Brian Knight

Reputation: 5041

It is often not very efficient to have functions run on the data being returned - as in the case of your ABS. I don't think you're in any trouble in this case, however. You could avoid the use of the function by trying something like:

OR (    
    (Addresses.Latitude IS NOT NULL)
    AND (Addresses.Longitude IS NOT NULL)
    AND ( (Addresses.Latitude - @SearchInLat) BETWEEN -0.5 AND 0.5)
    AND ( (Addresses.Longitude - @SearchInLng) BETWEEN -0.5 AND 0.5)

Upvotes: 5

Cade Roux
Cade Roux

Reputation: 89741

I think ABS and some builtin functions (like math functions) are very easily handled by the optimizer - others not so much (string and date operations).

In your particular case, I think you're OK with ABS.

Upvotes: 2

Related Questions