I have the need to find companies within a certain range of a zip code. Technically this is easy as I have the Lat/Lng for every US and Canadian zip code, but the MS SQL Query optimizer messes me up. First I had a simple function that calculates the distance between 2 zip codes, e.g.
Select * from Company where ZipToZipDistance('48111',C
ompany.Zip
) < 50
However that is really slow since it calculates the distance for every record. I pre-assign lat/lng to every Company, so this type of query is really fast:
Select * from Company where Company.Longitude between -83.683 AND -83.292
So I wrote a couple simple functions to give me the lower and upper longitude for a target zip code and range, thinking that it would be calculated once and used on an index for longitude, basically it is
CREATE FUNCTION dbo.UpperLng (@Zip varchar(50), @Range int) RETURNS float AS BEGIN
blah, blah, blah to clean up zip codes
select @Lng = avg(Lng), @Lat = avg(Lat) from zip where zip like @TempZip
set @Lng = @Lng + (@Range / (69.1 * Cos(@Lat / 57.3)))
return @Lng
And the function by itself works just fine. However, if I run a query like this:
Select * from Company where Company.Longitude between LowerLng('48111',10) AND UpperLng('48111',10)
The query optimizer goes nuts, figure out that I am doing a "zip like @TempZip" inside my functions, and gives me a hideous plan including things like
WHERE Company.Zip Like @TempZip for the upper function, another WHERE Company.zip Like @TempZip for the lower function, a gazillion Compute Scaler to calculate the upper/lower longitude for EVERY STINKING RECORD, and THEN does the compare to see if it is in range.
Is there any way I can force SQL into using the fixed result of my function instead of trying to calculate it for every record. I know I could put the whole thing into a stored procedure, but that would be inconvenient for a particular web app I am creating. Is there is a simple way to tell SQL "Calculate this first because it is a constant"
Start Free Trial
