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.
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
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
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
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
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
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 =
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
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:
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
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
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
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
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
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
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,
16 matches
Mail list logo
