31 Replies Latest reply on Mar 21, 2012 12:59 PM by -==cfSearching==-

    Help creating a stored procedure


      I have a query that is going to run many, many times per day, potentially hundreds of thousands so I want to make sure it's as quick as possible


      The purpose is to take a compiled IP number, look it up and relate it back to the country based on data in the table for current IP/countries


      There are currently 113,536 records in the country table, It's typically taking around 4ms to 5ms, so it's not exactly slow, but as the use of this particular lookup is going to increase significantly I'd like to make sure I have it as fast as possible.


      Here's the current code:


      <CFSET ipnumber = (#listgetat(remote_addr,1,'.')# *256*256*256) +  (#listgetat(remote_addr,2,'.')# *256*256) + (#listgetat(remote_addr,3,'.')#  *256) + #listgetat(remote_addr,4,'.')#>


      <CFQUERY name="GetCountry" DATASOURCE="#datasource#">

          SELECT TOP 1(countryshort) as countryshort

          FROM #ipcountry#

          WHERE ipfrom <= #ipnumber# and ipto >= #ipnumber#



      The first thought is to try to create a stored proc to see if that generates any performance gain, I've search and read over several documents on setting one up, but I have to admit, I just can't quite grasp it.


      I'm wondering if anybody would be kind enough to stick this query into a stored proc for me and let me know what CF code I need to execute it and read the result.


      Once I see this done I am sure I'll be able to push forward from there on my own in the future