Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
davidmal
Starting Member
19 Posts |
 Posted - 2008-08-21 : 12:51:25
|
| Greetings all,Please help a DBA challenged developer.I have an IP geolocation database with this schema:rngID int PK, Unique, ClusteredstartIpNum bigint IDX, Unique, Non-Clustered, Fillfactor=95%endIpNum bigint IDX, Unique, Non-Clustered, Fillfactor=95%country nchar(50)region nchar(50) city nchar(50) postalCode nchar(20) areaCode nchar(18) This table has 2,942,551 recordsWhen I execute a query to extract the locationselect * from IPCityState WHERE startIpNum <= 1147705496 *AND endIpNum >= 1147705496 **note - this value would normally passed as a parameterThe query takes 18-24 seconds. I can programmatically scrape a geolocation website faster than that (It's just not entirely legal so I'd rather not). This is the only query that will be being called on this table. That is why I only indexed the startIpNum and endIpNum columns.Any suggestions as to how to pull the data faster?Dave |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-08-21 : 13:02:52
|
| Are you really using * in your query? Have you tried a covering index: startIpNum,endIpNum with include columns of whatever your query returns (please return only what you need and not *).Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-08-21 : 13:20:03
|
| What is the nature of the data in the two columns startIPNum and endIPNum? ie: Does any given parameter value only have 1 row that would satisfy the query or are there many rows returned? If many, how many?If the data is designed to "trap" a row or range of rows surrounding one input value then a clustered index on your (startIPNum, endIPNum) may be perfect for that query (rather than a clustered index on mgID).But no matter what 20 seconds seems too long for 3m rows so Tara may be on to something or perhaps concurrent requests are blocking.Be One with the OptimizerTG |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-08-21 : 13:20:29
|
| What does execution plan say about it? Clustered index scan --Bookmarklookup. |
|
|
|
|
|
|
|
|
Need some performance help