CODEWITHSUNDEEP
sql
Shawn
Shawn

Reputation: 19793

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

Upvotes: 1

Views: 247

Answers (3)

Quassnoi
Quassnoi

Reputation: 425361

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

Upvotes: 1

George Mastros
George Mastros

Reputation: 24498

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.

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 3

Related Questions