One thing I would do to speed things up a little is use cfqueryparam. Not only is this good to make sure the user isn't performing a sql injection attack, but cfqueryparam will send a compiled query to the DB, which can speed things up (otherwise the DB has to compile every query, including all the ones in your loop). You'll have to play around to see if this speeds things up or not.
> -----Original Message----- > From: Ken [mailto:[EMAIL PROTECTED] > Sent: Friday, December 02, 2005 4:33 PM > To: CF-Talk > Subject: Re: Stored Proc > > I know where you are coming from, Munson. However, believe > me, I have good > reason to do that. > > I have a table db_postalcodes of all US zipcodes and their > latitutde and > longitudes. I am building a store locator app. > > My logic is to take the zipcode entered by the user, fetch > the latitutde and > logitude for it, compare it with the zipcodes of our store > locations. My CFC > calculates the distance between 2 given zipcodes. Then I > present the least > distance to the user. > > So far, I have code that looks like this: (It is very taxing on the > database): > <cfobject component="Resources" name="ObjRes"> > <cfset userzip=86403> <!--- zip entered by user---> > > <cfquery name="GetUserZip" datasource="#request.datasource#"> > select latitude, longitude from db_postalcodes where > ZIPCode='#userzip#' > </cfquery> > > <cfset lat1=#GetUserZip.latitude#> > <cfset lon1=#GetUserZip.longitude#> > > <cfquery name="GetQDZips" datasource="#request.datasource#"> > select db_users.user_id, db_users.user_postal AS QDZip from db_users > where db_users.user_postal is not null > AND db_users.country_id=218 > AND user_id IN (Select user_ID from db_quikdrop where > inactive IS NULL) > </cfquery> > <cfset distlist=""> > > <cfloop query="GetQDZips"> > <cfquery name="GetQDZip" datasource="#request.datasource#"> > select latitude, longitude from db_postalcodes where ZIPCode = '#Left( > GetQDZips.QDZip,5)#' > </cfquery> > <cfset lat2=#GetQDZip.latitude#> > <cfset lon2=#GetQDZip.longitude#> > <cfif IsNumeric(lat2) AND IsNumeric(lon2)> > <cfset distance=ObjRes.LatLonDist(lat1,lon1,lat2,lon2,'nm')> > > <cfset distlist= ListAppend(distlist,distance)> > </cfif> > > </cfloop> > <cfset distlist=listsort(distlist,"numeric")> > <cfoutput>#distlist#</cfoutput> > > I would appreciate ways to make this code more efficient. > Thanks, > K > > > On 12/2/05, Munson, Jacob <[EMAIL PROTECTED]> wrote: > > > > > As they say in CF good practices. Make your database do > the work for > > you. > > > > That's news to me. Why take a good cfc that is working > just fine and > > turn it into a stored proc? > > > > Assuming that is the way you are going to go, regardless of my > > ramblings, what DB are you running? Stored procedures are > different in > > every DB... > > > > --------------- > > > > > > [INFO] -- Access Manager: > > This transmission may contain information that is > privileged, confidential > > and/or exempt from disclosure under applicable law. If you > are not the > > intended recipient, you are hereby notified that any > disclosure, copying, > > distribution, or use of the information contained herein > (including any > > reliance thereon) is STRICTLY PROHIBITED. If you received > this transmission > > in error, please immediately contact the sender and destroy > the material in > > its entirety, whether in electronic or hard copy format. > Thank you. A2 > > > > > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Discover CFTicket - The leading ColdFusion Help Desk and Trouble Ticket application http://www.houseoffusion.com/banners/view.cfm?bannerid=48 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:225983 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
