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
