We're designing an application where we need to determine if a specified ip
address (represented as a bigint) is between a iplow and iphigh column we've
got in a sql/server table.

Currently I've got iplow and iphigh columns defined as bigint. I've also
created a clustered, unique index containing both iplow and iphigh columns.
We've got slightly over 5 million rows in our table.

How can we ideally design the table/indexes to make these queries execute as
quickly as possible. Queries such as:

select * from ip2location where iplow > 12345 and iphigh < 678910

these queries are also slow.

Thanks.

Rob


select * from ip2location where 1288141274 between iplow and
iphigh

can take up to 20 seconds to return 1 row!

I've also tried range based queries ie:

Some things you might try.

1) Add a nonclustered index with keys iphigh, iplow.
2) You don't say if there is a lot of bytes in each row other than just the
iplow and iphigh columns. If you have other columns that add significantly
to the width of each row, you might want to try also adding a nonclustered
index with keys iplow, iphigh.
3) Write your queries to give the Optimizer all the you can. So that
you scan the smallest amount of the index for a query. For example,
select * from ip2location where iplow > 12345 and iphigh < 678910
will start in the index at iplow > 12345 and scan all the way to the end of
the index. But since you presumably know that in every row iplow <= iphigh,
try writing the query as
select * from ip2location where iplow > 12345 and iplow < 678910
and iphigh > 12345 and iphigh < 678910
That way the scan will start at the first row where iplow > 12345 and stop
at the first row where iplow >= 678910 instead of scanning to the end of the
index
4) select * from ip2location where 1288141274 between iplow and iphigh
will scan the whole index. Rewrite it so that it only has to scan part of
the index, like
select * from ip2location where iplow <= 1288141274 and iphigh >= 1288141274

Tom



"robpoz" <robpoz@newsgroup.nospam> wrote in message
news:715A2B29-68FB-43C4-8B11-29D040CD4605@microsoft.com...
> We're designing an application where we need to determine if a specified
> ip
> address (represented as a bigint) is between a iplow and iphigh column
> we've
> got in a sql/server table.
>
> Currently I've got iplow and iphigh columns defined as bigint. I've also
> created a clustered, unique index containing both iplow and iphigh
> columns.
> We've got slightly over 5 million rows in our table.
>
> How can we ideally design the table/indexes to make these queries execute
> as
> quickly as possible. Queries such as:
>
> select * from ip2location where iplow > 12345 and iphigh < 678910
>
> these queries are also slow.
>
> Thanks.
>
> Rob
>
>
> select * from ip2location where 1288141274 between iplow and
> iphigh
>
> can take up to 20 seconds to return 1 row!
>
> I've also tried range based queries ie:
>
>

Do you need select * ? If not, a nonclustered index on the columns in the
select list would cover the query and may result in a seek. It basically
looks like you are getting a bunch of rows too so SQL is going to scan to
leverage sequential IO. If you have to get this much data, you might have to
go with partitioned view and\or table to make the scan smaller. How rows of
the 5 million does that query return?

--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio


"robpoz" <robpoz@newsgroup.nospam> wrote in message
news:715A2B29-68FB-43C4-8B11-29D040CD4605@microsoft.com...
> We're designing an application where we need to determine if a specified
> ip
> address (represented as a bigint) is between a iplow and iphigh column
> we've
> got in a sql/server table.
>
> Currently I've got iplow and iphigh columns defined as bigint. I've also
> created a clustered, unique index containing both iplow and iphigh
> columns.
> We've got slightly over 5 million rows in our table.
>
> How can we ideally design the table/indexes to make these queries execute
> as
> quickly as possible. Queries such as:
>
> select * from ip2location where iplow > 12345 and iphigh < 678910
>
> these queries are also slow.
>
> Thanks.
>
> Rob
>
>
> select * from ip2location where 1288141274 between iplow and
> iphigh
>
> can take up to 20 seconds to return 1 row!
>
> I've also tried range based queries ie:
>
>

Hi Rob,
I agree with Jason's suggestion. If there are many columns in the table, I
do not recommend that you use "SELECT *" to retrieve about 5 million rows
at once. Is it possible for you to use TOP clause in your T-SQL statement?

Also I recommend that you reorganize your index to see if it s. You can
run "ALTER INDEX...WITH REORGNIZE" clause or "DBCC INDEXDEFRAG" (deprecated
in SQL Server 2005).

Hope this s. Please feel free to let us know if you have any other
questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======

Tom, Jason, Charles,

Thanks very much for your response.

Sorry, I simplfied the select statement to minimize typing while posting my
question. Our select statement is not select * but only selecting one varchar
field. Also, the rows are not wide at all - maybe 5-6 columns.

Is it better to have a nonclustered index instead of clustered? I thought
clustered indexes were better for range searches and the table contains a
unique clustered index containing two columns.

I'll try the suggestions you've pointed out.

Thanks.

Rob

"robpoz" wrote:

> We're designing an application where we need to determine if a specified ip
> address (represented as a bigint) is between a iplow and iphigh column we've
> got in a sql/server table.
>
> Currently I've got iplow and iphigh columns defined as bigint. I've also
> created a clustered, unique index containing both iplow and iphigh columns.
> We've got slightly over 5 million rows in our table.
>
> How can we ideally design the table/indexes to make these queries execute as
> quickly as possible. Queries such as:
>
> select * from ip2location where iplow > 12345 and iphigh < 678910
>
> these queries are also slow.
>
> Thanks.
>
> Rob
>
>
> select * from ip2location where 1288141274 between iplow and
> iphigh
>
> can take up to 20 seconds to return 1 row!
>
> I've also tried range based queries ie:
>
>

Something else that may not be clear in my initial post:

The select statement should return a max of one row. The table contains 5
million rows or so.

Thanks.

Rob

When I suggested adding a nonclustered index on iplow, iphigh, I meant you
could try adding that nonclustered index in addition to your current
clustered index, not in replace of it. But that would probably only be
ful if your rows are wide (in the sense of average number of bytes per
row, not the number of columns).

However, in a different message in this thread, you stated that you knew
there could only be one match. Is this because you can't have overlapping
ip ranges in your table? If that's true, you could try writing your queries
to take advantage of that fact. For example, replace

select * from ip2location where iplow > 12345 and iphigh < 678910

with

select <column list>
from ip2location p
where p.iplow = (select min(iplow) from ip2location p2 where p2.iplow >
12345)
and p.iphigh < 678910

and replace

select * from ip2location where 1288141274 between iplow and iphigh

with

select <column list>
from ip2location p
where p.iplow = (select max(iplow) from ip2location p2 where p2.iplow <=
1288141274)
and p.iphigh >= 1288141274

Tom


"robpoz" <robpoz@newsgroup.nospam> wrote in message
news:BC61A958-D139-4F42-B3A3-A2273B4D88E7@microsoft.com...
> Tom, Jason, Charles,
>
> Thanks very much for your response.
>
> Sorry, I simplfied the select statement to minimize typing while posting
> my
> question. Our select statement is not select * but only selecting one
> varchar
> field. Also, the rows are not wide at all - maybe 5-6 columns.
>
> Is it better to have a nonclustered index instead of clustered? I thought
> clustered indexes were better for range searches and the table contains a
> unique clustered index containing two columns.
>
> I'll try the suggestions you've pointed out.
>
> Thanks.
>
> Rob
>
> "robpoz" wrote:
>
>> We're designing an application where we need to determine if a specified
>> ip
>> address (represented as a bigint) is between a iplow and iphigh column
>> we've
>> got in a sql/server table.
>>
>> Currently I've got iplow and iphigh columns defined as bigint. I've also
>> created a clustered, unique index containing both iplow and iphigh
>> columns.
>> We've got slightly over 5 million rows in our table.
>>
>> How can we ideally design the table/indexes to make these queries execute
>> as
>> quickly as possible. Queries such as:
>>
>> select * from ip2location where iplow > 12345 and iphigh < 678910
>>
>> these queries are also slow.
>>
>> Thanks.
>>
>> Rob
>>
>>
>> select * from ip2location where 1288141274 between iplow
>> and
>> iphigh
>>
>> can take up to 20 seconds to return 1 row!
>>
>> I've also tried range based queries ie:
>>
>>

Hi Rob,
Regarding this issue, I intend to think that this issue is related to I/O
bottle neck. I would like to recommend that you refer to the following
article for troubleshooting the query performance:
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/pro.../tsprfprb.mspx

Please feel free to let me know if you have any other questions or
concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =======
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
================================================== =======
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== =======