Help with Inner Join Replace
Posted on 2010-11-16
This query takes 2 seconds to run:
inner join (SELECT Distinct zips.CityName, zips.ProvinceAbbr
FROM dbo.postalcodes AS [zips]
INNER JOIN dbo.CalculateBoundary(49.136510, - 122.831860, 45.000000, 'kilometers') AS bounds ON 1 = 1
WHERE [zips].[Latitude] BETWEEN [bounds].[South] AND [bounds].[North]
AND [zips].[Longitude] BETWEEN [bounds].[West] AND [bounds].[East]
AND [zips].[Latitude] <> 0
AND [zips].[Longitude] <> 0
AND (dbo.CalculateDistance(49.136510, - 122.831860, zips.Latitude, zips.Longitude, 'kilometers') <= 21.000000)
AND [zips].[CityType] = 'D') c on
c.cityname=widecitylist.citt and c.provinceabbr = widecitylist.state
and all i do is change the last line to:
c.cityname=Replace (widecitylist.city,'-',' ') and c.provinceabbr = widecitylist.state
And now it takes over 20 seconds, which is unacceptable.
What is happening is that the query pulls all fields from dbo.zips in a radius of 21 kilometers of Surrey BC, which is 32,000 (canada has hundreds of thousands of zipcodes, sometimes 10,000+ in a single city). I then select only the distinct city, which is 24.
From those 24 results, I simply want to update WideCityList item = 1, but I needed the replace function because all cities in WideCityList have a "-" instead of a space for cities with two words. I dont know why it takes so horribly long to run the query simply by changing the last line...I'm assuming that somewhere in there its doing an inner join using REPLACE on all 32000 results before DISTINCT is processed? I only want to perform the inner join with Replace against the 24 results of the inside select statement.
hoping this makes sense! Any ideas?