Solved

# Help with Inner Join Replace

Posted on 2010-11-16
286 Views
This query takes 2 seconds to run:

update WideCityList
set Item=1
from widecitylist
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?
0
Question by:arthurh88
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 58

Accepted Solution

cyberkiwi earned 500 total points
ID: 34151619
Try this form:

update WideCityList
set Item=1
from (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
inner join widecitylist on
c.cityname=Replace(widecitylist.city,'-',' ')  and c.provinceabbr = widecitylist.state
option(force order)

If there are only 24 matches, it should be reasonably quick.
If that doesn't work, reverse the condition

inner join widecitylist on
Replace(c.cityname,' ','-')=widecitylist.city  and c.provinceabbr = widecitylist.state

The reversal changes 24 rows, but uses that to do an index lookup in the widecitylist table (assuming one can be used)
0

Author Comment

ID: 34151631
"inner join widecitylist on
Replace(c.cityname,' ','-')=widecitylist.city  and c.provinceabbr = widecitylist.state"

that worked!!  (the reversal)  thanks so much.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
###### Suggested Courses
Course of the Month9 days, 10 hours left to enroll