The tag you want is <cfstoredproc>

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

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

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.

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

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.

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.

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.

What database product and version are you using?

I'd start with reviewing Microsoft's documentation.

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

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/

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

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.