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

    Help creating a stored procedure

    ACS LLC Community Member

      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

        • 1. Re: Help creating a stored procedure
          Dan Bracuk Community Member

          The tag you want is <cfstoredproc>

          • 2. Re: Help creating a stored procedure
            ACS LLC Community Member

            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

            • 3. Re: Help creating a stored procedure
              HiTopp

              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

              • 4. Re: Help creating a stored procedure
                ACS LLC Community Member

                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>

                • 5. Re: Help creating a stored procedure
                  HiTopp Community Member

                  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

                  • 6. Re: Help creating a stored procedure
                    JR "Bob" Dobbs Community Member

                    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.

                    • 7. Re: Help creating a stored procedure
                      ACS LLC Community Member

                      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

                      • 8. Re: Help creating a stored procedure
                        ACS LLC Community Member

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

                        • 9. Re: Help creating a stored procedure
                          HiTopp Community Member

                          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.

                          • 10. Re: Help creating a stored procedure
                            JR "Bob" Dobbs Community Member

                            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-7f 6f.html

                             

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

                            • 11. Re: Help creating a stored procedure
                              -==cfSearching==- Community Member

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

                              • 12. Re: Help creating a stored procedure
                                ACS LLC Community Member

                                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?

                                • 13. Re: Help creating a stored procedure
                                  -==cfSearching==- Community Member

                                  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.

                                  • 14. Re: Help creating a stored procedure
                                    ACS LLC Community Member

                                    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?

                                    • 15. Re: Help creating a stored procedure
                                      JR "Bob" Dobbs Community Member

                                      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.

                                      • 16. Re: Help creating a stored procedure
                                        ACS LLC Community Member

                                        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.

                                        • 17. Re: Help creating a stored procedure
                                          JR "Bob" Dobbs Community Member

                                          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.

                                          • 18. Re: Help creating a stored procedure
                                            -==cfSearching==- Community Member

                                            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.

                                            • 19. Re: Help creating a stored procedure
                                              ACS LLC Community Member

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

                                              • 20. Re: Help creating a stored procedure
                                                -==cfSearching==- Community Member

                                                And what do your execution plans say?

                                                • 21. Re: Help creating a stored procedure
                                                  ACS LLC Community Member

                                                  I am the first to admit that I am no SQL Guru, I know enough to get things functioning with no issues. Right now I just add an index and go with default values, I see you can change the order and maybe other parameters. Is default setting not enough? Appreciate any pointers on this one

                                                   

                                                  Thanks

                                                   

                                                  Mark

                                                  • 22. Re: Help creating a stored procedure
                                                    ACS LLC Community Member

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

                                                    • 23. Re: Help creating a stored procedure
                                                      JR "Bob" Dobbs Community Member

                                                      What database product and version are you using?

                                                      • 24. Re: Help creating a stored procedure
                                                        ACS LLC Community Member

                                                        MS SQLServer 2008

                                                        • 25. Re: Help creating a stored procedure
                                                          JR "Bob" Dobbs Community Member

                                                          I'd start with reviewing Microsoft's documentation.

                                                           

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

                                                          • 26. Re: Help creating a stored procedure
                                                            ACS LLC Community Member

                                                            It's on the to-do list

                                                            • 27. Re: Help creating a stored procedure
                                                              -==cfSearching==- Community Member

                                                              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/

                                                              • 28. Re: Help creating a stored procedure
                                                                ACS LLC Community Member

                                                                I think it might be easier to just throw a bigger server at it if it starts to become a problem!

                                                                 

                                                                I don't think any of my code or SQL is that bad, it's just probably not what a guru would write, but it might suffice. Getting to that next level looks like a long haul!

                                                                • 29. Re: Help creating a stored procedure
                                                                  -==cfSearching==- Community Member

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

                                                                  • 31. Re: Help creating a stored procedure
                                                                    -==cfSearching==- Community Member

                                                                    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.