Help creating a stored procedure

[ACS LLC]

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

[-__cfSearching__-]

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.

[ACS LLC]

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

[-__cfSearching__-]

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/

[ACS LLC]

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!

[-__cfSearching__-]

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

[ACS LLC]

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

[Guest]

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.