• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

Help creating a stored procedure

Enthusiast ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

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

TOPICS
Database access

Views

4.8K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

The tag you want is <cfstoredproc>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 17, 2012 Mar 17, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

Hi HiTopp

Thanks for the feedback, I'll definitely connect with the SQL forum 🙂

There are two columns in the table and I need to find the number I have as to where it fits in

1000,2000

2001,3000

etc

so if I have 2500 it is in the 2nd row

Looks like you missed off the ipto >= bit 🙂

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

    SELECT TOP 1(countryshort) as countryshort

    FROM #ipcountry#

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

</CFQUERY>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

ACS LLC wrote:

Looks like you missed off the ipto >= bit

BETWEEN and AND are T-Sql operators that duplicate the exact same thing as <= and >=.

http://msdn.microsoft.com/en-us/library/ms187922.aspx

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

Bear in mind that database servers, such as Microsoft SQL Server, will cache the execution plan for parameterized SQL queries which will result in performance gains similar to use of a stored procedure. 

You might try using CFQUERYPARAM in your queries.  This is also recommended to help prevent SQL injection attacks.

You might also create indexes on the ipfrom column on your table.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

Sorry for my ignorance on this but how do you define a parameterized SQL Query?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

ACS LLC wrote:

Sorry for my ignorance on this but how do you define a parameterized SQL Query?

A parameterized query uses bound parameters.  In ColdFusion one uses cfqueryparam to add bound parameters.

For example (this assumes that the ipfrom column is a bigint column):

<cfquery name="countryLookup">

    SELECT country

    FROM ipcountry

    WHERE ipfrom <= <cfqueryparam value="#ipnumber#" cfsqltype="cf_sql_integer">

        AND ipto >= <cfqueryparam value="#ipnumber#" cfsqltype="cf_sql_integer">;

</cfquery>

References:

CFQUERYPARAM

http://help.adobe.com/en_US/ColdFusion/9.0/CFMLRef/WSc3ff6d0ea77859461172e0811cbec22c24-7f6f.html

Message was edited by: JR \"Bob\" Dobbs Corrected typo in cfqueryparam tags.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

So you're saying that by using the cfqueryparam that the execution will actually be faster?

(Btw Yes it's a bigint, so I presume all cfsqltype are integer if it's tinyint, int, bigint)

<cfquery name="countryLookup">

    SELECT country

    FROM ipcountry

    WHERE ipfrom <= <cfqueryparam value="#ipnumber#" cfsqltype="cf_sql_integer">

        AND ipto >= <cfqueryparam value="#ipnumber#" cfsqltype="cf_sql_integer">;

</cfquery>

btw Is a query like this as quick as a stored proc? Is there any performance benefit to the stored proc over this approach?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

Yes it's a bigint, I presume all cfsqltype are integer if it's tinyint, int, bigint

No, each has a different capacity, which is why they are assigned different cfsqltypes. Refer to the documentation for cfqueryparam linked above.

So you're saying that by using the cfqueryparam that the execution will actually be faster?

Bind parameters encourage databases to cache the query execution plan to enhance performance when the same statement is executed multiple times. But many factors affect execution and performance. There is no one-size-fits-all answer. You need to use the available SQL query tools and examine the actual execution plans for your queries yourself.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

ahhh ok, JR "Bob" had mentioned, that the command presumed it was a bigint which through me because the type in the example was cf_sqp_integer

So what I actually need is

<cfquery name="countryLookup">

    SELECT country

    FROM ipcountry

    WHERE ipfrom <= <cfqueryparam value="#ipnumber#" cfsqltype="CF_SQL_BIGINT">

        AND ipto >= <cfqueryparam value="#ipnumber#" cfsqltype="CF_SQL_BIGINT">;

</cfquery>

Do you believe that there are cases where the same CFquery which is bound correctly vs a stored proc could give different performance results? Differences that are worth actually bothering about?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

ACS LLC wrote:

Do you believe that there are cases where the same CFquery which is bound correctly vs a stored proc could give different performance results? Differences that are worth actually bothering about?

The performance benfits of using stored procs come from the database server re-using the same execution plan.  Execution plans can also be re-used for SQL statements with bound parameters, so the performance benefits *should* be about the same.  You would need to use your database's performance monitoring tools to test this assumption.  Not all database systems act the same in how they optomize queries.

I also agree with cfsearching's statement that there is no single right solution to improving query performance.  That being said; I *suspect* that creating an index on the columns used in your WHERE clause will be more beneficial than creating a stored proc.  I encourage you to investigate the performance monitoring tools available for your database product.  Forum users don't have access to your organization's database so any advise we give is limited.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

I placed the same code, one with my old SQL the other with a the CFQUERYPARAM and ran repeated tests, now it might not be a great tests because it's just me repeating it rather than a request for different data, the results were 3ms now and again, some 5ms and mainly 4ms response on both, didn't really notice any difference, of course on such a fast query unless it's under a ton of load with many requests for different responses I guess that I won't know for sure.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

This probably means that the query is not complicated for the server to parse, but that the work of finding the rows that match your WHERE clause is time consuming.  I will repeat the recommendation that you index the column(s) used in your WHERE clause.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

the results were 3ms now and again, some 5ms and mainly 4ms response on both, didn't really notice any difference,

Execution time alone is not a good measurement. Like I mentioned earlier, you must examine the execution plan. Look at how the statement is processed. Does it result in a table scan? Or does it utilize indexes? Does it peform an index scan or seek? Etecetera. That is one of the best tools in your arsenal for guaging performance (which you seem to be avoiding *hint*, *hint*).

I *suspect* that creating an index on the columns used in your WHERE clause will be more beneficial than creating a stored proc.

For such a simple query, agreed. But do not just guess. Use your query tools to examine the plans and create an effective index.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

Sorry, I forgot to mention earlier that the columns ipfrom and ipto are indexed

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

And what do your execution plans say?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

that proves my SQL level I guess.. what are the execution plans?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

What database product and version are you using?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

MS SQLServer 2008

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

I'd start with reviewing Microsoft's documentation.

http://msdn.microsoft.com/en-us/library/ms191227.aspx

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

It's on the to-do list

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

>: It's on the to-do list

Well move it to the head of the list. Not to be rude, but in the time you have spent on this thread alone, you could have digested a significant amount of the documentation already

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Mar 21, 2012 Mar 21, 2012

Copy link to clipboard

Copied

ouch 😉

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation