Conducting A Radius Search Via Postcodes - Cursor Issue?

Hi, I’ve developed the following stored procedure to query a table of services following a user search. The user can specify a radius from a certain location(UK postcode) in which to search, and its with this Im having a few problems.





Here’s my method:





STEP1 - I first check the table of services and insert all services matching the specified criteria (regardless of location) into a temporary table (#tMatches).





STEP 2 - I get the postcode entered by the user and retrieve its co-ordinates.





STEP 3 – I open a cursor and loop through the records in #tMatches. For each record, I first retrieve their co-ordinates and then use a bit of Pythagoras to work out whether its location falls within the radius specified by the user.





STEP 4 – If the record falls outside the range specified by the user, I deleted from #tMatches.





STEP 5 – I bind the remaining (matching) records from the #tMatches to a dataset.





Now, I think that seems pretty simple, and the easiest way to achieve what I’m trying to do. How then when querying just 600 records am I finding it times out? I think I’ve pinned it down to the STEP 3 (cursor), but not being the SQL expert I’d like to be, I’ve no further ideas where I’m going wrong? Any ideas?





The following is an excerpt from the script - don't worry about the fact several parameters may not be declared.





Many thanks





======================================





--STEP 1


CREATE TABLE #tMatches


(


record_uid uniqueidentifier,


service_name varchar(500),


service_address varchar(500),


service_description varchar(500),


GRE integer,


GRN integer


)


BEGIN


INSERT INTO #tMatches


(record_uid,service_name,service_address,service_description,GRE,GRN)


SELECT service_loc_uid,title_return,service_address,service_overview,GRE,GRN FROM v_RecordsLive_short WHERE (title_return LIKE '%' + @lookingfor + '%' OR service_category LIKE '%' + @lookingfor + '%' OR service_overview LIKE '%' + @lookingfor + '%')


END





-STEP 2





DECLARE @UserCoordX integer


DECLARE @UserCoordY integer


--1) USE POSTCODE DATA FOR REGION ONLY


SELECT @UserCoordX = GRE,@UserCoordY = GRN FROM tPCD WHERE tPCD.PCD = @postcode





--STEP3





--open a cursor containing record ids with co-ordinates


DECLARE @record_uid uniqueidentifier


DECLARE @CoordX integer


DECLARE @CoordY integer


DECLARE @XLen integer


DECLARE @YLen integer


DECLARE @range real





DECLARE locs_cursor CURSOR FOR


SELECT record_uid,GRE,GRN FROM #tMatches





OPEN locs_cursor





FETCH NEXT FROM locs_cursor


INTO @record_uid,@CoordX,@CoordY





WHILE @@FETCH_STATUS = 0


BEGIN


--calculate range from entered postcode using pythagorus


SET @XLen = Abs(@UserCoordX - @CoordX)


SET @YLen = Abs(@UserCoordY - @CoordY)


SET @range = SQRT((@XLen * @XLen) + (@YLen * @YLen))





END





--convert range to miles


SET @range = COALESCE(@range,0)


IF (@range > 0)


SET @range = @range/160.9





--IF OUT OF RANGE, DELETE RECORD FROM TABLE STRAIGHTAWAY


IF @range > CAST(@miles as float)


DELETE FROM #tMatches WHERE record_uid = @record_uid





--get next record


FETCH NEXT FROM locs_cursor


INTO @record_uid,@CoordX,@CoordY





CLOSE locs_cursor


DEALLOCATE locs_cursor





--------------------RETURN ALL RESULTS-----------------------------------------------


SELECT record_uid,service_name,service_address,service_description FROM #tMatches





DROP Table #tMatches