Indexing on a query

Question

I have a query that takes too long and frequently times out. It's a proximity based zip code search table-value function. Is there anyway to index based on the query, so it doesn't have to recalculate all of these values every time? The postal code and zip code list combined is over a million rows.

Here is the table function.

Create  FUNCTION [dbo].[ZipsInRadius] (@zipCode varchar(15), 
 @radius int, @unit char(1))
RETURNS @areaResults TABLE(
 Zip    varchar    (30),
 City    varchar    (255),
 St    varchar (20),
 Lat    decimal    (16,12),
 Long    decimal (16,12))    
BEGIN

 DECLARE @iStartLat decimal(16, 12)
 DECLARE @iStartLong decimal(16, 12)
 SELECT
  @iStartLat = CAST(Latitude AS decimal(16, 12)), 
  @iStartLong = CAST(Longitude AS decimal(16, 12)) 
 FROM zip
 WHERE zipcode  LIKE @zipCode + '%'
 SELECT
  @iStartLat = CAST(Latitude AS decimal(16, 12)), 
  @iStartLong = CAST(Longitude AS decimal(16, 12)) 
 FROM postalcode
 WHERE postalcode  LIKE @zipCode + '%'
 DECLARE @latRange decimal(16, 12)
 DECLARE @longRange decimal(16, 12)

 IF (@unit = 'K')         --Get distance in kilometers
  BEGIN
   SELECT @LatRange = 
      (CAST(@radius / ((6076.0 / 5280.0) * 60.0) 
    AS decimal(16, 12))) * 0.621371
   SELECT @LongRange = 
      (@radius / (((cos(@iStartLat * pi() / 180.0) * 6076.0) 
    / 5280.0) * 60)) * 0.621371
  END
 ELSE                     --Get distance in miles (the default)
  BEGIN
   SELECT @LatRange = CAST(@radius / ((6076.0 / 5280.0) * 60.0) 
    AS decimal(16, 12))
   SELECT @LongRange = 
      @radius / (((cos(@iStartLat * pi() / 180.0) * 6076.0) 
    / 5280.0) * 60)
  END

 DECLARE @lowLatitude decimal(16, 12)
 DECLARE @highLatitude decimal(16, 12)
 DECLARE @lowLongitude decimal (16, 12)
 DECLARE @highLongitude decimal (16, 12)
 SELECT @lowLatitude = @iStartLat - @latRange
 SELECT @highLatitude = @iStartLat + @latRange
 SELECT @lowLongitude = @iStartLong - @longRange
 SELECT @highLongitude = @iStartLong + @longRange

 INSERT INTO @areaResults (zip, city, st, lat, long) 
   SELECT ZIPcode, CITY, STate, LATitude, LONGitude
   FROM Zip Z
   WHERE Z.Latitude <= @highLatitude
      AND Z.Latitude >= @lowLatitude
   AND Z.Longitude >= @lowLongitude
      AND Z.Longitude <= @highLongitude     
  INSERT INTO @areaResults (zip, city, st, lat, long)
   SELECT postalcode, CITY, province, LATitude, LONGitude
   FROM postalcode z
   WHERE Z.Latitude <= @highLatitude
      AND Z.Latitude >= @lowLatitude
   AND Z.Longitude >= @lowLongitude
      AND Z.Longitude <= @highLongitude
 RETURN
END

Answer 1

It takes me milliseconds, maybe your approach is wrong take a look here: SQL Server Zipcode Latitude/Longitude proximity distance search 2000/2005 version

or for the 2008 version by using the geography datatype here: SQL Server 2008 Proximity Search With The Geography Data Type

Answer 2

I would recommend an multi-column index on Longitude and Latitude.

It's good that you are using a bounding box, which would ordinarily speed up your query. With the index I mention, you should see huge improvements.

On a side note, you have your latitude/longitudes stored in a Decimal(16,12). 12 digits of precision is probably TONS more than you need. The fifth digit (in lat/long units) represents approximately 3 feet. so.. the 12th digit might actually represent nano-meters (or less). By using a smaller data type, your tables (and indexes) will be more efficient. This is especially true with zip code searchs because the lat/longs you have are a point representing the center of a zip code, the position isn't very exact to begin with. For longitude, I usually use Decimal(8,5). Since Latitude is usually in the range -90 to 90, you can get away with Decimal(7,5) for latitude.

Answer 3

You may try to force INDEX JOIN on your indexes and see if it helps:

CREATE INDEX ix_zip_lat ON zip(lat)

CREATE INDEX ix_zip_long ON zip(long)

SELECT * FROM zip
WITH  (INDEX(ix_zip_lat), INDEX (ix_zip_long))
WHERE lat BETWEEN @lowlat and @hilat
      AND long BETWEEN @lowlong and @hilong