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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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/
Copy link to clipboard
Copied
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!
Copy link to clipboard
Copied
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==-
Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.