Need Performance Tips - Zip Code Locator

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

The ultimate cost for this query is coming from the FTS query. Basically, sqlserver will have to wait for the result to come back for each freetexttable() before it can join to the base table. And also, the resultset returned from the freetexttable() is not indexed. So, if there are lots of data in the resultset, the main query can take a long time to join. In case you have lots of data return from FTS, you might want to create a temp/working table to hold the data. You can then index them before joining with the base table.