Optimizing IP Range Search?

I have a simple query and table, and I would like to know which indexing is efficient on this kind of table and query.

In my table I have 3 columns

CREATE TABLE mYTable(ipFrom BIGINT, ipto BIGINT, url NVARCHAR(255))

and I am running this simple query.

SELECT url 
FROM MyTable 
WHERE ipto = somevalue

I have also created indexes on all 3 columns clustered on ipFrom and non-clustered on rest of 2. But this query giving me really poor performance in terms of CPU & reads.

Any suggestions.

What i have implemented is i like to redirect user based on the IP address.
I have stored several IP ranges from different areas and states and redirect users on the basis of their IP to appropriate URL.

Yes i think i inserted the data in wrong manner in both columns(and will do column rename later), but point here is to minimize the CPU.

When i look into the execution plan there it converts data in where clause, i dont know why it is converting data in where clause. There is something like this

|--Clustered Index Seek(OBJECT:([T].[TC]), SEEK:([T].[C] > Convert([@V])

I'm assuming there is a typo in the question as per Martin's comment.

A problem with a single range seek on either ipFrom or ipTo is that on average it will need to seek half the table. An alternative approach is

1) Add a computed column, on something like:

ALTER TABLE MyTable
  ADD granule 
AS CASE WHEN (ipTo-ipFrom)<16 THEN 1 WHEN (ipTo-ipFrom)<256 THEN 2 ELSE 3 END;

2) Index the computed column and ipFrom

CREATE ClUSTERED INDEX IX ON MyTable(granule, ipFrom );

3) Query like this:

SELECT url
FROM   MyTable
WHERE  granule = 1
       AND ipFrom BETWEEN @somevalue - 16 AND @somevalue
       AND ipTo >= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 2
       AND ipFrom BETWEEN @somevalue - 256 AND @somevalue
       AND ipTo >= @somevalue
UNION ALL
SELECT url
FROM   MyTable
WHERE  granule = 3
       AND ipFrom = @somevalue;

Precisely how you define the 'granule' function will depend on the spead of ipto-ipfrom in your data.

StackExchange Database Administrators Q#14894, answer score: 6