Need Performance Tips - Zip Code Locator

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need Performance Tips - Zip Code Locator

Author

Topic

jmag99
Starting Member

2 Posts

Posted - 2006-08-30 : 21:50:09

I have a stored procedure used to lookup ad's similar to ebay. We want to allow the customer to search based on their zip code in relation to the items location. This query works but it takes too long. We only want to return the top 100 records. It also uses indexed searching for the main search terms. Any ideas what we can do to improve performance?

CREATE PROCEDURE Search
@SearchText varchar(200),
@CategoryID int = Null,
@TxtDesc bit = 0,
@PriceMin money = 0,
@PriceMax money = 250000,
@ZipCode varchar(5) = Null,
@Distance smallint = 0,
@ManMul int = 1000,
@ModMul int = 1000,
@TitMul int = 100,
@DesMul int = 1
AS
BEGIN
DECLARE @CenterLat float
DECLARE @CenterLon float

-- Earth Radius In Miles
DECLARE @EarthRadius float
SET @EarthRadius = 3958.76

-- Determine Lat/Lon For User's Zip Code
SELECT @CenterLat = Lat,
@CenterLon = Long
FROM List_ZipCodes
WHERE Zip_Code = @ZipCode

DECLARE @CntXAxis float
DECLARE @CntYAxis float
DECLARE @CntZAxis float

SET @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
SET @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
SET @CntZAxis = sin(radians(@CenterLat))

SELECT TOP(100)
C.Classified_ID,
Manufacturer,
Model,
Title
FROM Classifieds C
INNER JOIN Classifieds_Categories ON C.Classified_ID = Classifieds_Categories.Classified_ID
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Manufacturer), @SearchText)AS f1 ON C.Classified_ID = f1.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Model), @SearchText)AS f2 ON C.Classified_ID = f2.[Key]
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, (Title), @SearchText)AS f3 ON C.Classified_ID = f3.[Key]
INNER JOIN List_ZipCodes AS ZC ON ZC.Zip_Code = C.Shipping_FromZip
WHERE ((COALESCE(f1.[Rank], 0)* @ManMul) + (COALESCE(f2.[Rank], 0)* @ModMul) + (COALESCE(f3.[Rank], 0) * @TitMul)) > 500
AND Active = 1
AND Price_Asking >= @PriceMin
AND Price_Asking <= @PriceMax
AND (Category_ID = COALESCE(@CategoryID, Category_ID) OR Category_ID IN (SELECT Category_ID FROM List_Categories WHERE Parent_Category_ID = @CategoryID))
AND Shipping_FromZip Is Not Null
AND (@EarthRadius * acos((cos(radians(ZC.Lat)) * cos(radians(ZC.Long)))*@CntXAxis + (cos(radians(ZC.Lat)) * sin(radians(ZC.Long)))*@CntYAxis + (sin(radians(ZC.Lat)))*@CntZAxis)) <= @Distance
ORDER BY ((COALESCE(f1.[Rank], 0)* @ManMul) + (COALESCE(f2.[Rank], 0)* @ModMul) + (COALESCE(f3.[Rank], 0) * @TitMul)) DESC
END

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-31 : 00:24:57

/* I have not tested this, and the query may need some tweaking, but let me know if the speed is any better */

CREATE Function dbo.FMilesLatLong (
@P_Lat1 decimal(8,4) =0 ,
@P_Long1 decimal(8,4) =0 ,
@P_Lat2 decimal(8,4) =0 ,
@P_Long2 decimal(8,4) =0)

Returns decimal(10,2)
)
As
Begin
/*************************************************************/
/* Routine to calc miles distance from two points on earth */
/* specified in latitude/longitude pairs.*/
/* Inputs are "degrees.minutes" ie: 33.0654 */
/*************************************************************/
set nocount on
declare @Lat1 decimal(28,10)
declare @Long1 decimal(28,10)
declare @Lat2 decimal(28,10)
declare @Long2 decimal(28,10)
declare @d decimal(28,10)
declare @P_DiffMiles decimal(10,2)
/* Default return value */
set @P_DiffMiles = 0

/* Convert to radians */
set @Lat1 = @P_Lat1 / 57.2958
set @Long1 = @P_Long1 / 57.2958
set @Lat2 = @P_Lat2 / 57.2958
set @Long2 = @P_Long2 / 57.2958

/* Calc distance */
set @d = (Sin(@Lat1) * Sin(@Lat2)) + (Cos(@Lat1) * Cos(@Lat2) * Cos(@Long2 - @Long1))

/* Convert to miles */
if @d <> 0
begin
set @P_DiffMiles = 3958.75 * Atan(Sqrt(1 - power(@d, 2)) / @d);
end
end
Return @P_DiffMiles

CREATE PROCEDURE Search
@SearchText varchar(200),
@CategoryID int = Null,
@TxtDesc bit = 0,
@PriceMin money = 0,
@PriceMax money = 250000,
@ZipCode varchar(5) = Null,
@Distance int, --I am using this for the "Milesaway <= @Distance"
@ManMul int = 1000,
@ModMul int = 1000,
@TitMul int = 100,
@DesMul int = 1,
AS
BEGIN
DECLARE @CenterLat float
DECLARE @CenterLon float

-- Determine Lat/Lon For User's Zip Code
SELECT @CenterLat = Lat,
@CenterLon = Long
FROM List_ZipCodes
WHERE Zip_Code = @ZipCode

SELECT TOP(100)
C.Classified_ID,
Manufacturer,
Model,
Title
FROM Classifieds C
INNER JOIN List_ZipCodes AS ZC ON ZC.Zip_Code = C.Shipping_FromZip
LEFT OUTER JOIN FREETEXTTABLE(Classifieds, *, @SearchText)AS f1 ON C.Classified_ID = f1.[Key]
WHERE
dbo.FMilesLatLong(@CenterLat ,@CenterLon,zc.Lat,zc.Lon) <= @Distance)
AND Active = 1
AND Price_Asking >= @PriceMin
AND Price_Asking <= @PriceMax
AND (c.Category_ID = COALESCE(@CategoryID, c.Category_ID)
OR c.Category_ID IN (SELECT Category_ID FROM List_Categories WHERE Parent_Category_ID = @CategoryID))
AND c.Shipping_FromZip Is Not Null
Order By f1.[Rank] desc
END

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-31 : 04:32:50

What does the query plan look like and what is your index situation? Statements like this are bound to give you bad performance regardless of index situation:

WHERE ((COALESCE(f1.[Rank], 0)* @ManMul) + (COALESCE(f2.[Rank], 0)* @ModMul) + (COALESCE(f3.[Rank], 0) * @TitMul)) > 500

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-31 : 10:10:11

If you are only dealing with U.S. zip codes, then factoring in the curvature of the Earth may be fancy but it has questionable practical value in relation to its query cost.

You may get better performance if you first filter on some absolute values (such as the absolute difference in longitude and the absolute difference in latitude), and then use trigonometry to fine-tune the distances between the subset of zip codes that are left.

jmag99
Starting Member

2 Posts

Posted - 2006-08-31 : 15:01:36

Thanks, this is a big help. We have also started doing the Lat/Long lookup when the record is added rather than looking it up on the fly like we were doing. We will try the suggestion of using basic trig since our maximum distance to search is only 200 miles.

Is there a way to rank the fields in the free text search in a different way then we are doing it now (with 3 left joins and then multiplying the rank by a factor?).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources