Yes, that's a great idea. I didn't only because I wanted to make sure I caught everything within larger radii (like 100 miles, and even some 250 mile ones). This means in the New England area, you have to watch out for those small states. So I decided to err on the side of a one-time math crunch. :P
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 13:47 To: SQL Subject: RE: I need some indexing advice I do zipcode radius searches constantly and for performance, I did the same thing as option 2 below. The only thing I did different, was I only did radius from the SCF (first 3 characters of the Zip code). All SCF's are geographically located Thank You, Christian Watt Webmaster SkillPath Seminars [EMAIL PROTECTED] -----Original Message----- From: Raster, Tim [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 11:00 AM To: SQL Subject: RE: I need some indexing advice Well, off the cuff, I'd index zips.zip for the 1st query. Your 2nd query is probably destined to be slow (depending on how many records are in dealers_geo). I don't see a lot of opportunity to index that one, because you are forcing a lot of math calculations on each row, and then applying your whereclause to that math, not a field in the table. I see 2 choices for you.... 1. Try to divide the 2nd query's workload by 50 by pulling by state first. For example, if the user pulled 14000 zipcode, you know to only pull state of NY stuff (and maybe its surrounding states, which would usually only had about 5-6 more). In other words, don't bother calculating and searching for all 50 states worth of dealers... So add a column to your dealers table that holds the State, and pass that as additional Whereclause criteria, and index that column. That can help your query greatly. 2. I did one of these once. I pre-calculated all zipcodes' distances from each other, and only kept the ones that were <= 100 miles (or some number). I jammed those into a table that had 3 columns in it: Zip, TargetZip, and DistanceInMiles. It had almost 10 million rows in it, but this is data that does not change (rarely anyway), *and* I pre-calc all the math stuff once, and store the results, so no more expensive math within each search. I built an index Zip column, and another on (Zip, TargetZip, DistanceInMiles) columns. This allowed me to pull together a query of the starting Zip (what the user wanted), target zips (in this case, your dealers in the area), and put some where clause criteria where DistanceInMiles < [some number]. This turned out to be VERY fast (like fractional seconds for searches). Its only drawback is it takes a little diskspace (about 4 gb for mine, but probably less because I didn't even try to keep disk down), and a lot of up front work to pre-calc the stuff (it took me 4 Pentium 2 200-400 PC about 3 days to compute it all, back then). -----Original Message----- From: Phillip B [mailto:[EMAIL PROTECTED] Sent: Monday, September 08, 2003 09:43 To: SQL Subject: I need some indexing advice This is what I have done. I took a zipcode table and my dealer table and made a view that pretty much joins the two on the zip code. Neither of these are indexed yet. I then wrote the following two queries to find dealers. The second query can take almost 8 seconds to run and is the view of the dealer and zip code table. I'm not that comfortable with indexing my tables yet so I was looking for some advice on what to index in the tables. SELECT zip, lat, long, city, state FROM zips WHERE zip='#form.passedzipcode#' SELECT TOP 15 lat, long, name, city, state, zipcode, area_code, phone, 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) AS distance FROM dealers_geo #WHERE# 3963 * (ACOS((SIN(#passedzip.lat#/57.2958) * SIN(lat/57.2958)) + (COS(#passedzip.lat#/57.2958) * COS(lat/57.2958) * COS(long/57.2958 - #passedzip.long#/57.2958)))) <= #form.passedradius# ORDER BY distance Phillip B. www.LoungeRoyale.com www.FillWorks.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:> Signup for the Fusion Authority news alert and keep up with the latest news in ColdFusion and related topics. http://www.fusionauthority.com/signup.cfm
