quote:

---

Originally posted by Peso

Also is IP range varchar? Or INT?

---

quote:

---

Originally posted by Peso

Yes, a normal

CREATE UNIQUE CLUSTERED INDEX IX_IP ON tbl_IP (IP_From, IP_To)

will do well.

And remember to use FillFactor 100 when creating.

---

quote:

---

Originally posted by icx

quote:

---

Originally posted by Peso

Yes, a normal

CREATE UNIQUE CLUSTERED INDEX IX_IP ON tbl_IP (IP_From, IP_To)

will do well.

And remember to use FillFactor 100 when creating.

---

Ok, will try this now to see if it helps.... Many thanks.

---

quote:

---

Originally posted by Peso

You wrote before that a search took about second before.
How long time does it take now?

---

SELECT *
FROM ( SELECT TOP n * FROM #IPSample ) AS S
INNER JOIN #Range AS R
  ON S.ip BETWEEN R.ip_from AND R.ip_to

  |--Nested Loops(Inner Join, OUTER REFERENCES:([tempdb].[dbo].[#IPSample].[ip]))
       |--Top(TOP EXPRESSION:((98)))
       |    |--Table Scan(OBJECT:([tempdb].[dbo].[#IPSample]))
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Range] AS
[R]), SEEK:([R].[ip_from] <= [tempdb].[dbo].[#IPSample].[ip]),
WHERE:([tempdb].[dbo].[#IPSample].[ip]<=[tempdb].[dbo].[#Range].[ip_to]
as [R].[ip_to]) ORDERED FORWARD)

  |--Nested Loops(Inner Join, OUTER REFERENCES:([R].[ip_from], [R].[ip_to]))
       |--Index Scan(OBJECT:([tempdb].[dbo].[#Range] AS [R]))
       |--Index Spool(SEEK:([tempdb].[dbo].[#IPSample].[ip] >=
[tempdb].[dbo].[#Range].[ip_from] as [R].[ip_from] AND
[tempdb].[dbo].[#IPSample].[ip] <= [tempdb].[dbo].[#Range].[ip_to] as
[R].[ip_to]))
            |--Top(TOP EXPRESSION:((99)))
                 |--Table Scan(OBJECT:([tempdb].[dbo].[#IPSample]))

DROP TABLE #R0
DROP TABLE #R1
DROP TABLE #Range

-- generate 4 million random 32-bit values to form the start of each range:
SELECT CAST(FLOOR(POWER(2.0, 32) * RAND(CAST(NEWID() AS binary(4)))) AS bigint) as ip
INTO #R0
FROM dbo.Numbers AS N1, dbo.Numbers AS N2
WHERE N2.n < 40

-- get the distinct ones and give them ids in ip order:
SELECT ROW_NUMBER() OVER (ORDER BY ip) AS range_id, ip
INTO #R1
FROM #R0
GROUP BY ip

-- cluster on range id:
ALTER TABLE #R1 ALTER COLUMN range_id int NOT NULL
ALTER TABLE #R1 ADD PRIMARY KEY (range_id)

-- get the end of one range from the start of the next range
-- (i.e. ranges are non-overlapping and contiguous):
CREATE TABLE #Range (
  range_id int PRIMARY KEY NONCLUSTERED,
  ip_from bigint NOT NULL,
  ip_to bigint NOT NULL
)

INSERT INTO #Range
SELECT R_from.range_id, R_from.ip AS ip_from, R_to.ip - 1 AS ip_to
FROM #R1 AS R_from
INNER JOIN #R1 AS R_to
  ON R_from.range_id = R_to.range_id - 1

-- throw away every fourth range so there are some holes and ~3 million ranges:
DELETE FROM #Range
WHERE (range_id % 4) = 0

-- create the unique clustered index:
CREATE UNIQUE CLUSTERED INDEX Range_from_to ON #Range(ip_from, ip_to) WITH (FILLFACTOR = 100)

-- create a million random IP addresses
SELECT CAST(FLOOR(POWER(2.0, 32) * RAND(CAST(NEWID() AS binary(4)))) AS bigint) as ip
INTO #IPSample
FROM dbo.Numbers AS N1, dbo.Numbers AS N2
WHERE N2.n < 10

-- execution plan show estimated row count for result of 709780000000!
SELECT *
FROM #IPSample AS S
INNER JOIN #Range AS R
  ON S.ip BETWEEN R.ip_from AND R.ip_to

-- if we know that the ranges are non-overlapping, single IP address
-- lookups can be done much more quickly like this:
SELECT *
FROM ( SELECT TOP 1000 * FROM #IPSample ) AS S
OUTER APPLY (
    SELECT *
    FROM (
        SELECT TOP 1 *
        FROM #Range AS R
        WHERE S.ip >= R.ip_from
        ORDER BY R.ip_from DESC
      ) AS R
    WHERE S.ip <= R.ip_to
  ) AS R

quote:

---

Originally posted by icx

The trouble is, the ip2location database is so large that executing a query against it to find which range a particular IP address is within takes me 1 second. Multiply that by 1,000,000 log rows, and Houston - we have a problem.

---

quote:

---

Originally posted by Peso

quote:

---

Originally posted by icx

The trouble is, the ip2location database is so large that executing a query against it to find which range a particular IP address is within takes me 1 second. Multiply that by 1,000,000 log rows, and Houston - we have a problem.

---

This is the part I read.
I interpreted this as the ASP page read the client IP address, searched the IP-table and then wrote to the log table.

---

IpFrom  IpTo  City 
1       3     London

1  London
2  London
3  London

quote:

---

Originally posted by Peso

I have to test your code tonight.
Maybe a

SELECT TOP 1 * FROM IpLocation WHERE FromIP < @IpToSearch ORDER BY FromIP DESC

would suffice?

---

         0	 268435455
 268435456	 536870911
 536870912	 805306367
 805306368	1073741823
1073741824	1342177279
1342177280	1610612735
1610612736	1879048191
1879048192	2147483647
2147483648	2415919103
2415919104	2684354559
2684354560	2952790015
2952790016	3221225471
3221225472	3489660927
3489660928	3758096383
3758096384	4026531839
4026531840	4294967295