Skip to main content
A
ACS LLC
Inspiring
March 17, 2012
Question

Help creating a stored procedure

  • March 17, 2012
  • 1 reply
  • 6664 views

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

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
March 17, 2012

The tag you want is <cfstoredproc>

A
ACS LLCAuthor
Inspiring
March 17, 2012

Do you think you could show me how this would look in the stored Proc? I think I could figure out the CF side, it's the SQL stored Proc part that's really getting me

Btw countryshort is a char(2) and ipfrom and ipto are both big integers

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

    SELECT TOP 1(countryshort) as countryshort

    FROM #ipcountry#

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

</CFQUERY>

Thanks!

Mark

A
Anonymous
March 21, 2012

CREATE PROCEDURE yourSpName

(

     @ipnumber BIGINT

)

AS

BEGIN

     DECLARE @sql NVARCHAR(MAX)

     SET @sql = N'

          SELECT TOP 1(countryshort) as countryshort

          FROM ipCountryTable

          WHERE ipfrom BETWEEN @ipnumber AND @ipnumber -- Not sure why you are using a range here

     '

     EXEC sp_executesql @sql,

          N'@ipnumber BIGINT',

          @ipnumber

END

I'm not great with stored procedures either, but I think this should help you.  SQL Server should be able to get you started as well with creating the procedure.  Just make sure you create the procedure within the databse where your IP table is located.

But for the future, this should really have been asked in a SQL Server forum. http://social.msdn.microsoft.com/Forums/en/category/sqlserver

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices