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

    Help creating a stored procedure

    ACS LLC Newcomer

      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#

      </CFQUERY>

       

      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

       

      Thanks

       

      Mark