Indexing on a query

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

From stackoverflow

• 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

• 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.

  Shawn Simon : thanks... i added the indexes, but i still problems. the zip in question actually returns 98000 rows (its in toronto), and takes 41 seconds to run...

  Shawn Simon : ill hve to try the decimal thing, it looks like the two tables have different types, its probably doing a ton of conversions

  Shawn Simon : eventually i just added an index on zip and then inner join it on the table i was searching first. it reduced the table value function by a lot

• 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