Skip navigation
Currently Being Moderated

Help creating a stored procedure

Mar 17, 2012 11:57 AM

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

 
Replies
  • Currently Being Moderated
    Mar 17, 2012 3:56 PM   in reply to ACS LLC

    The tag you want is <cfstoredproc>

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 7:21 AM   in reply to ACS LLC

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 8:13 AM   in reply to ACS LLC

    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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 8:23 AM   in reply to HiTopp

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 8:33 AM   in reply to ACS LLC

    ACS LLC wrote:

     

    The logic does not seem to be correct

     

    You have:

    WHERE ipfrom BETWEEN @ipnumber AND @ipnumber

     

    I have:

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

     

    I am looking for a result where ipFROM <= and another column ipTO >=

     

    Bt your statement is just using ipfrom

     

    My mistake.  I misread your query.  I did not realize you were specifying two different table columns.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 8:44 AM   in reply to ACS LLC

    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/WSc3ff6d0ea77859461 172e0811cbec22c24-7f6f.html

     

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 8:51 AM   in reply to JR \"Bob\" Dobbs

    JR \"Bob\" Dobbs wrote:

     

    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.


    Agreed. Just wrapping a query in a stored procedures does not mean it is going to run faster. Look at the table's indexes and examine the execution plan to see how it can be improved. And you really should be using cfqueryparam in all of your queries. Not just for performance, but because you are risking sql injection attacks without it.

     

    HiTopp wrote

    EXEC sp_executesql @sql,

     

    Since the table name is hard coded, there really is not a need for dynamic sql in this query.

     

    Message was edited by: -==cfSearching==-

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 10:30 AM   in reply to ACS LLC

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 10:59 AM   in reply to ACS LLC

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 11:04 AM   in reply to ACS LLC

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 11:25 AM   in reply to JR \"Bob\" Dobbs

    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.

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 11:57 AM   in reply to ACS LLC

    And what do your execution plans say?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 12:16 PM   in reply to ACS LLC

    What database product and version are you using?

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 12:22 PM   in reply to ACS LLC

    I'd start with reviewing Microsoft's documentation.

     

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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 12:33 PM   in reply to ACS LLC

    Is default setting not enough?

     

    Lol, I have no idea. The "best settings" will vary based on your application, data and queries. You are the only one that has access to

    them. So only you can determine if the settings have the desired effect.

     

    You have to dig in and start looking at the execution plans. There is a

    lot to digest, and they take a while to fully understand. But that is

    the only way to examine the efficiency of your sql, determine if

    indexes are utilized, compare performance, etcetera.

     

    http://msdn.microsoft.com/en-us/library/ms189562%28v=SQL.90%29.aspx

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

    http://www.simple-talk.com/sql/performance/execution-plan-basics/

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 12:35 PM   in reply to ACS LLC

    >: 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

     
    |
    Mark as:
  • Currently Being Moderated
    Mar 21, 2012 12:59 PM   in reply to ACS LLC

    Sorry, but sometimes the truth hurts ;-) Honestly, I do not always relish pouring over thick tomes of documentation myself. But it has to be done because general advice can only carry me so far. Ultimately, I am the one responsible for coding and maintaining my applications. Since there is no learning by osmosis (though sometimes I wish there were ;-) some things I just have to learn by reading and doing myself. It is just part of the job.

     
    |
    Mark as:

More Like This

  • Retrieving data ...

Bookmarked By (0)

Answers + Points = Status

  • 10 points awarded for Correct Answers
  • 5 points awarded for Helpful Answers
  • 10,000+ points
  • 1,001-10,000 points
  • 501-1,000 points
  • 5-500 points