Difficult Stored Procedure tuning (to me at least)

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

Transact-SQL (2000)

Difficult Stored Procedure tuning (to me at least)

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2007-06-23 : 16:33:24

In my limited ability I cobbled together the following stored procedure.

Thanks to member ehorn for posting the zip code radius formula.

SP is returning the results desired, problem is the speed. According to the execution plan about 64% of the cost is spent on the "SELECT Zip , City, MileRadius" query with about 49% of that on the filter.

Where should my efforts be focused on for speeding it up?

Thanks!


Alter  PROC [dbo].[TS_zip_radi_Job]
	@Zip char(5) ,
	@Radius int
AS
SET NOCOUNT ON
DECLARE @lat1 float, 
	@long1 float
SELECT  @lat1= lat,
        @long1 = lon 
FROM TSPlaces
WHERE Zip = @Zip
SELECT Zip , City, MileRadius
INTO #temp
FROM
(
	SELECT  Zip, City,3958.75 * ( Atan(Sqrt(1 - Power(((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((lon/57.2958) - (@Long1/57.2958)))), 2)) / 
		((Sin(@Lat1/57.2958) * Sin(lat/57.2958)) + (Cos(@Lat1/57.2958) * Cos(lat/57.2958) * Cos((lon/57.2958) - (@Long1/57.2958)))))) MileRadius
	FROM TSPlaces
) a


WHERE a.MileRadius >= 0 

AND  a.MileRadius <= @Radius

ORDER BY MileRadius

SELECT     dbo.TSJobPostDetail.JobTitle, dbo.TSJobPostDetail.PostingSummary1, dbo.TSJobPostDetail.PostingSummary2, dbo.TSJobPostDetail.Description, 
                      dbo.TSLocationDetail.LocationName



FROM         dbo.TSLocationDetail INNER JOIN
                      dbo.TSActivePosts ON dbo.TSLocationDetail.TSLocationID = dbo.TSActivePosts.TSLocationID INNER JOIN
                      #temp ON dbo.TSLocationDetail.Zip = #temp.zip INNER JOIN
                      dbo.TSJobPostDetail ON dbo.TSActivePosts.TSJobPostingID = dbo.TSJobPostDetail.TSJobPostingID
WHERE     (dbo.TSJobPostDetail.ActiveFlag = '1')

DROP TABLE #temp

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-23 : 16:43:50

This has been discussed many times here at SQLTeam. Search for "Haversine formula" and/or "Great Circle".

Peter Larsson
Helsingborg, Sweden

chrispy
Posting Yak Master

107 Posts

Posted - 2007-06-23 : 17:17:12

Peter,

Thanks for the search tip. I would of never figured out to search for that.

I followed the post at
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81359

From there I created the GreatCircle function and now my elect looks like this:

FROM
(
	SELECT  Zip, City, 
		MileRadius = dbo.F_GREAT_CIRCLE_DISTANCE(@lat1,@long1,lat,lon)
	FROM TSPlaces
) a

That took the "SELECT Zip, City....." Down to 51% of the execution with the filter now only being 13% of the costs. Seems the table scan at 67% hurting me the most.

With something like 42,000 zip codes I am wondering if going down the rows is the best approach? Or am I missing something?

Kristen
Test

22859 Posts

Posted - 2007-06-25 : 08:44:18

Do you need to know the distance for all of them, or are you just trying to find things within "10 miles" or somesuch?

'Coz if its the latter it is quicker to search for Latitude / Longitude which is within a "box" which encloses a 10-mile-radius-circle, and then for that short-list to use Great Circle or somesuch to calculate which ones are actually within the circle.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Distance+of+Zip,Postcodes

and also: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=85363

Kristen

chrispy
Posting Yak Master

107 Posts

Posted - 2007-07-10 : 11:42:49

Kristen,

Sorry I must of missed your post. Good news is drawing a "box" is exactly what I did and it is working great.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources