Re: Transact-SQL help
Can you post the query execution plan? It sounds like you forgot to add an index. I have anice getdistance function that calcs the distance between two zip codes. **snip** However, that is very slow. It's very fast if I pass in the lat and long. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268984 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Can you post the query execution plan? It sounds like you forgot to add an index. That or the you're killing the optimizer with the SELECT *; bookmark lookups can kill a DB. I've got my zip code database proximity thing all figured out. I have a nice getdistance function that calcs the distance between two zip codes. It returns a result set containing all the zip codes within 25 miles of 27502. But I can't use that in an IN clause... so what do I do? Rick ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268987 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Query optimization didn't have anything to do with it. I was running getDistance(zip1,zip2) instead a WHERE IN clause. My getDistance() function, which was based on Russ' would actually do two selects based on the zip code arguments. For example select prospect, name, address, city, state, zip from prospects where zip in ( select zipcode from zipcodes where getDistance(prospects.zip,'27502') 25) Let's say there were 100,000 rows in the prospect table. My original solution would've caused the getDistance() function to be called 100,000 times, and each time it would run two select queries to get the latitude and longitude, and then calc the distance. Effectively running 200,000 little select queries. Indexes weren't going to help =) At any rate, I got everything working super fast and blogged all about it. Rick ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:269029 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact-SQL help
Rick,
Did you take a look at the query that I sent earlier? It takes only 3
seconds to run on my machine.
Russ
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 11:49 AM
To: CF-Talk
Subject: OT: Transact-SQL help
I've got my zip code database proximity thing all figured out. I have a
nice getdistance function that calcs the distance between two zip codes.
syntax of my function: getDistance(zip1,long1,lat1,zip2,long2,lat2)
taking either the zip code or the lat/long for each...
I can now do:
SELECT *
FROM prospects A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
getDistance('27502',0.0,0.0,'',B.latitude,B.longitude)
)
However, that is very slow. It's very fast if I pass in the lat and long.
So I've written a stored procedure that when executed looks like this:
sp_zipcodes '27502' 25
It returns a result set containing all the zip codes within 25 miles of
27502.
But I can't use that in an IN clause... so what do I do?
Rick
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268844
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
Russ, if you're referring to this one:
*
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742
*http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268742
works fine if you want to pass in the lat/long directly, but I'm trying to
come up with a way to do it off zip code. your function got really slow
when I added functionality inside to do lat/long lookups, because the
function executes for each zip code you're comparing - up to 42000.
However, in attemping to explain all this, I've actually worked out a
solution:
The following query worked pretty well, given that TB907 (the address
table) contains nearly 900,000 records. (Don't blame me for the table name,
blame BSR, it's their product)
DECLARE @long1 decimal(5,2)
DECLARE @lat1 decimal(5,2);
SELECT @long1 = dbo.getlongitude('27502');
SELECT @lat1 = dbo.getLatitude('27502');
SELECT A.*
FROM WEBREPORTS.dbo.TB907 A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) 25
)
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268853
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact-SQL help
No, I'm referring to:
http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818
Russ
-Original Message-
From: Rick Root [mailto:[EMAIL PROTECTED]
Sent: Tuesday, February 06, 2007 12:42 PM
To: CF-Talk
Subject: Re: Transact-SQL help
Russ, if you're referring to this one:
*
http://www.houseoffusion.com/groups/CF-
Talk/thread.cfm/threadid:50203#268742
*http://www.houseoffusion.com/groups/CF-
Talk/thread.cfm/threadid:50203#268742
works fine if you want to pass in the lat/long directly, but I'm trying to
come up with a way to do it off zip code. your function got really slow
when I added functionality inside to do lat/long lookups, because the
function executes for each zip code you're comparing - up to 42000.
However, in attemping to explain all this, I've actually worked out a
solution:
The following query worked pretty well, given that TB907 (the address
table) contains nearly 900,000 records. (Don't blame me for the table
name,
blame BSR, it's their product)
DECLARE @long1 decimal(5,2)
DECLARE @lat1 decimal(5,2);
SELECT @long1 = dbo.getlongitude('27502');
SELECT @lat1 = dbo.getLatitude('27502');
SELECT A.*
FROM WEBREPORTS.dbo.TB907 A
WHERE
zipcode in
(
SELECT B.zipcode
FROM zipcodes B
WHERE
dbo.getDistanceBetween('',@lat1,@long1,'',B.latitude,B.longitude) 25
)
--
I'm not certified, but I have been told that I'm certifiable...
Visit http://www.opensourcecf.com today!
~|
Upgrade to Adobe ColdFusion MX7
Experience Flex 2 MX7 integration create powerful cross-platform RIAs
http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268855
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
Russ wrote: No, I'm referring to: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818 That one is pretty quick here... SELECT b.* FROM tblzipcodes a CROSS JOIN tmpzipcodes b WHERE dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 AND a.zip = '27614' Took 1412 ms. I limited the latitude side of things with... SELECT b.* FROM tmpzipcodes a CROSS JOIN tmpzipcodes b WHERE a.zip = '27614' AND b.latitude a.latitude + (10*.014457) AND b.latitude a.latitude - (10*.014457) AND dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 which only took 170ms I think that .014457 is right...basically 1 degree/69.172 miles. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268863 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
On 2/6/07, Russ [EMAIL PROTECTED] wrote: No, I'm referring to: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818 Ah, no I hadn't seen that one. I've never used CROSS JOIN before.. interesting. However, my final solution actually does the same thing in about 100ms for a 10 mile radius of 27502. It actually does it in about 1500ms, but I added some code to only look at zip codes whose lat/long were within a certain range, at jochem's suggestion. If I look at latitudes and longitutdes of 2.0 +/-, it improved the query performance from 1542ms to 109ms. Here's my solution - using your original getDistance function. DECLARE @lat1 decimal(5,2); DECLARE @long1 decimal(5,2); DECLARE @zip1 char(5); SET @zip1 = '27502' SELECT @lat1 = LATITUDE from ADSPRD.dbo.zipcodes where zipcode = @zip1; SELECT @long1 = LONGITUDE from ADSPRD.dbo.zipcodes where zipcode = @zip1; SELECT B.CITY, B.STATE, B.zipcode FROM ADSPRD.dbo.zipcodes B WHERE B.LATITUDE BETWEEN @lat1-2.0 and @lat1+2.0 AND B.LONGITUDE BETWEEN @long1-2.0 and @long1+2.0 AND ADSPRD.dbo.getDistance(@lat1,@long1,B.latitude,B.longitude) 10 It's definately not as simple as your cross join solution, but it's the equivalent of lightning fast by comparison. Thanks for everyone's help on this! Rick ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268870 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
so many solutions, so little time! I like how you're limiting by latitude only and using the radius as well. Doing that actually lowered my execution time to 94ms ... =) On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: Russ wrote: No, I'm referring to: http://www.houseoffusion.com/groups/CF-Talk/thread.cfm/threadid:50203#268818 That one is pretty quick here... SELECT b.* FROM tblzipcodes a CROSS JOIN tmpzipcodes b WHERE dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 AND a.zip = '27614' Took 1412 ms. I limited the latitude side of things with... SELECT b.* FROM tmpzipcodes a CROSS JOIN tmpzipcodes b WHERE a.zip = '27614' AND b.latitude a.latitude + (10*.014457) AND b.latitude a.latitude - (10*.014457) AND dbo.getDistance(a.latitude,a.longitude,b.latitude,b.longitude) 10 which only took 170ms I think that .014457 is right...basically 1 degree/69.172 miles. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268871 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Rick Root wrote: I like how you're limiting by latitude only and using the radius as well. Thinking about limiting it by longitude made my head hurt...I thought about using some larger constant (like the 2 degrees that you used), but in Alaska, 2 degrees longitude only equates to about 44 miles in some places. Limiting by latitude at least gets the bulk of the comparisons out of the way. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268875 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Actually, I did a limitation by longitude as well, because at the equator, the fudge factor is the same (approximatley 69 miles per degree)... everything in the US is less than that but I figured what the heck. So I draw the box on both lat and long, knowing that the longitude will actually encompass MORE than the area I'm looking for... the getDistance() function actually does the work of exact distances anyway. As long as the box is bigger than necessary, it's all good. I was thinking it might actually be wise to use a range factor GREATER than 1/69 ... like 2/69...giving the latitude some wiggle room. But thinking about the math involved as to *WHY* I'd do that makes *MY* head hurt! On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: Rick Root wrote: I like how you're limiting by latitude only and using the radius as well. Thinking about limiting it by longitude made my head hurt...I thought about using some larger constant (like the 2 degrees that you used), but in Alaska, 2 degrees longitude only equates to about 44 miles in some places. Limiting by latitude at least gets the bulk of the comparisons out of the way. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268878 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
RE: Transact-SQL help
You can also not think about the math, precompute the distance tables, and then only keep data in there where the distance is x miles. Russ -Original Message- From: Rick Root [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 06, 2007 3:10 PM To: CF-Talk Subject: Re: Transact-SQL help Actually, I did a limitation by longitude as well, because at the equator, the fudge factor is the same (approximatley 69 miles per degree)... everything in the US is less than that but I figured what the heck. So I draw the box on both lat and long, knowing that the longitude will actually encompass MORE than the area I'm looking for... the getDistance() function actually does the work of exact distances anyway. As long as the box is bigger than necessary, it's all good. I was thinking it might actually be wise to use a range factor GREATER than 1/69 ... like 2/69...giving the latitude some wiggle room. But thinking about the math involved as to *WHY* I'd do that makes *MY* head hurt! On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: Rick Root wrote: I like how you're limiting by latitude only and using the radius as well. Thinking about limiting it by longitude made my head hurt...I thought about using some larger constant (like the 2 degrees that you used), but in Alaska, 2 degrees longitude only equates to about 44 miles in some places. Limiting by latitude at least gets the bulk of the comparisons out of the way. ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268882 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
I blogged all this here: http://www.opensourcecf.com/1/2007/02/Determining-Zip-Code-Proximity.cfm ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268883 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Transact-SQL help
Rick Root wrote: Actually, I did a limitation by longitude as well, because at the equator, the fudge factor is the same (approximatley 69 miles per degree)... everything in the US is less than that but I figured what the heck. So I draw the box on both lat and long, knowing that the longitude will actually encompass MORE than the area I'm looking for... the getDistance() function actually does the work of exact distances anyway. As long as the box is bigger than necessary, it's all good. I was thinking it might actually be wise to use a range factor GREATER than 1/69 ... like 2/69...giving the latitude some wiggle room. But thinking about the math involved as to *WHY* I'd do that makes *MY* head hurt! What calculation did you use to limit the longitude?...if you are using the same one as latitude, I'm not understanding... Let's take a latitude around the center of our home state of NC...35 degrees... At 35 a degree of longitude is approx 57 miles long (per http://www.csgnetwork.com/degreelenllavcalc.html) 1 degree longitude/57 miles = .017544 1 degree latitude/69.172 miles = .014457 ..014457 .017544 so you would be limiting too much of the longitude if you are only using the .014457 number. Are you doing something different? Or do I have it all wrong? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268884 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
We are quite lucky in that our Search Engine software has GeoSearch built in :-) it is damn impressive to have it however you get it though, for $5 it's a steal to get the data! This e-mail is from Reed Exhibitions (Gateway House, 28 The Quadrant, Richmond, Surrey, TW9 1DN, United Kingdom), a division of Reed Business, Registered in England, Number 678540. It contains information which is confidential and may also be privileged. It is for the exclusive use of the intended recipient(s). If you are not the intended recipient(s) please note that any form of distribution, copying or use of this communication or the information in it is strictly prohibited and may be unlawful. If you have received this communication in error please return it to the sender or call our switchboard on +44 (0) 20 89107910. The opinions expressed within this communication are not necessarily those expressed by Reed Exhibitions. Visit our website at http://www.reedexpo.com -Original Message- From: Rick Root To: CF-Talk Sent: Tue Feb 06 20:34:34 2007 Subject: Re: Transact-SQL help I blogged all this here: http://www.opensourcecf.com/1/2007/02/Determining-Zip-Code-Proximity.cfm ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268885 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Re: Transact-SQL help
Jim, you make an excellent point that I hadn'e considered. However, in my blog entry on the topic, I'm actually doubling the size of the rectangle for the longitude, so I'm doing @lat1-(@[EMAIL PROTECTED]) so even though my range factor technically should be larger, I actually double it anyway, and that compensates for pretty much any different (except maybe in Alaska, I'll have to check that) The factor I'm multiplying by is 0.028985507 Northern alaska would be 0.52ish so I suppose I should actually multiply by 4 (4/69) to compensate. Rick On 2/6/07, Jim Wright [EMAIL PROTECTED] wrote: What calculation did you use to limit the longitude?...if you are using the same one as latitude, I'm not understanding... Let's take a latitude around the center of our home state of NC...35 degrees... At 35 a degree of longitude is approx 57 miles long (per http://www.csgnetwork.com/degreelenllavcalc.html) 1 degree longitude/57 miles = .017544 1 degree latitude/69.172 miles = .014457 ..014457 .017544 so you would be limiting too much of the longitude if you are only using the .014457 number. Are you doing something different? Or do I have it all wrong? ~| Upgrade to Adobe ColdFusion MX7 Experience Flex 2 MX7 integration create powerful cross-platform RIAs http:http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:268933 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
