> <!--- Get Zip Codes That Match --->
This query is always a beast, but it can be made a bit more
efficient as it is now.
First step would be to calculate constant value expressions
outside the database, so you are certain the database will not
evaluate the expressions for every row. (I would expect most
databases to be smart enough about this, but why take chances?)
Second, make sure you pass in the values using cfqueryparam.
Third, I doubt you rally need that DISTINCT.
By now, your code looks like:
<cfscript>
// Conversion factors:
// degToUnit = 60; // 1 degree = 60 nautical miles
// degToUnit = 69; // 1 degree = 69 'land' miles
degToUnit = 111; // 1 degree = 111 kilometer
minLat = passedzip.latitude - passedradius/degToUnit;
maxLat = passedzip.latitude + passedradius/degToUnit;
minLon = passedzip.longitude - passedradius/degToUnit;
maxLon = passedzip.longitude + passedradius/degToUnit;
</cfscript>
<cfquery datasource="#application.dsn1#" name="getlocs">
SELECT
zipcode,
latitude,
longitude,
ROUND( blahblahblah) AS distance
FROM
zipcodes
WHERE
latitude BETWEEN <cfqueryparam #minLat#> AND <cfqueryparam #maxLat#>
AND
longitude BETWEEN <cfqueryparam #minLon#> AND <cfqueryparam
#maxLon#>
ORDER BY
distance
</cfquery>
Now all you have to do is make sure you have a proper (clustered)
index on latitude and longitude and the query should fly in every
database.
(If you can make the distance calculation code a UDF, do so.)
> <!--- Find The Records From The Zip Code List And Display The Distance Variable --->
> <cfoutput query="getlocs">
> <cfquery name="zip_find" datasource="#application.dsn1#">
> Select distinct member_id
> From Contact_info
> Where zip = '#getlocs.zipcode#'
> </cfquery>
> <cfif len(zip_find.member_id)>
> #zip_find.Member_id# Zip Code: #getlocs.zipcode# Distance: #getlocs.distance#<br>
> </cfif>
> </cfoutput>
The standard way to get this extra information is to join the
contact_info table to the zipcodes table. The query above becomes:
<cfquery datasource="#application.dsn1#" name="getlocs">
SELECT
ci.zipcode,
ci.member_id,
udfDistance( blahblahblah ) AS distance
FROM
zipcodes zc INNER JOIN Contact_info ci
ON zc.zipcode = ci.zip
WHERE
latitude BETWEEN <cfqueryparam #minLat#> AND <cfqueryparam #maxLat#>
AND
longitude BETWEEN <cfqueryparam #minLon#> AND <cfqueryparam
#maxLon#>
ORDER BY
distance
</cfquery>
There are several shortcuts.
You could just add a lattitude and longitude column to the
contact_info table and cut the zipcode table right out of it
(make sure they have a clustered index). Not normalized, but
might have better performance.
Some databases have special geometry datatypes with optimized
functions for distance calculations and special indexes for
overlapping areas. Usually not portable, but might have better
performance.
You can pre-calculate distances between zip codes, store those in
a third table and do an extra join.
Jochem
--
I don't get it
immigrants don't work
and steal our jobs
- Loesje
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
