[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Help with Inner Join Replace

Posted on 2010-11-16
2
Medium Priority
?
294 Views
Last Modified: 2012-05-10
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
Comment
Question by:arthurh88
2 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 2000 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

by:arthurh88
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question