SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Some query optimalisation
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

bjornh
Yak Posting Veteran

Netherlands
87 Posts

Posted - 12/04/2002 :  12:34:40  Show Profile  Reply with Quote
wow arnold, its copy past... :D thank you very very much. my first tests with some ip's came out good...

Bjorn

Go to Top of Page

bjornh
Yak Posting Veteran

Netherlands
87 Posts

Posted - 12/06/2002 :  06:50:09  Show Profile  Reply with Quote
Hi there, again,

Pff SQL is really giving me a headanche...

I used the code from Arnold and it works, but now, I want more...
The code works fine if you use 1 ip, but now I want multiple ip addressen to be looked-up.
I created a user-defined function from that code, but it really is a time-expensive way. How can I created 1 SQL statment from it, or what what other way give the best preformance?

Thanks..
Bjorn

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/06/2002 :  08:10:15  Show Profile  Reply with Quote
Ok. First, I'll just adjust what I wrote earlier to include another column in IP_Subnets. iplast will hold the last IP address in the subnet range (which means that the column I previously called ipstart should be called ipfirst, but never mind). This isn't vital, but it stops the query getting messy.

DROP TABLE IP_Subnets
CREATE TABLE IP_Subnets (
ipstart binary(4) PRIMARY KEY,
iplast binary(4) NOT NULL,
netmask binary(4) NOT NULL,
country char(2) NOT NULL
)

INSERT INTO IP_Subnets (ipstart, iplast, netmask, country)
SELECT ipstart,
CAST(CAST(ipstart AS int) | (netsize - 1) AS binary(4)) AS iplast,
netmask, country
FROM (
SELECT
CAST(CAST(PARSENAME(ipstart, 4) AS tinyint) AS binary(1)) +
CAST(CAST(PARSENAME(ipstart, 3) AS tinyint) AS binary(1)) +
CAST(CAST(PARSENAME(ipstart, 2) AS tinyint) AS binary(1)) +
CAST(CAST(PARSENAME(ipstart, 1) AS tinyint) AS binary(1)) AS ipstart,
netsize,
CAST(netsize - 1 AS binary(4)) AS netmask,
country
FROM IP_Staging
WHERE type = 'ipv4' AND ipstart <> '*'
) A

 
Next, we'll need some test addresses. Since arin.20021201.txt covers about half the 32 bit space, choosing random 32 bit values seems fairly reasonable.

CREATE TABLE IP_TestAddress (ip binary(4))

INSERT INTO IP_TestAddress
SELECT CAST(NEWID() AS binary(4))
FROM Numbers
WHERE n < 10000

 
Numbers is just a tally table being used to generate 10000 rows (mine happens to be 0-based and have 100000, hence the WHERE). It's relying on the 32 bits from NEWID() being "random enough". If you're on NT4, this won't be the case.

Given all that, you might think that it's just a question of doing a simple join on the two tables:

SELECT ip, ipstart, netmask, country
FROM IP_TestAddress
LEFT JOIN IP_Subnets ON ip BETWEEN ipstart AND iplast

 
This works, but it's really slow because SQL Server doesn't understand the correlation between ipstart and iplast -- in this case, that they don't overlap at all.
A faster way is to adapt what I wrote earlier:

SELECT ip, ipstart, netmask, country
FROM IP_TestAddress
LEFT JOIN IP_Subnets
ON ipstart = (
SELECT MAX(ipstart)
FROM IP_Subnets
WHERE ip >= ipstart
)
AND ip <= iplast

 
Again, I've used a LEFT join so you can see the ones that fail to match a subnet.
I'm sure there are faster ways than that, but hopefully it'll do for now.


Go to Top of Page

bjornh
Yak Posting Veteran

Netherlands
87 Posts

Posted - 12/06/2002 :  11:15:57  Show Profile  Reply with Quote
mmm I tested this version, and it still slows down the server, I think i'll create a extra column with the ip's from my visitors, for the landID. I think that's an better option. Or do you think otherwise?

Bjorn



Edited by - bjornH on 12/06/2002 11:16:31
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 12/06/2002 :  11:25:52  Show Profile  Reply with Quote
Sorry, I don't understand what you mean. An extra column where?


Go to Top of Page

bjornh
Yak Posting Veteran

Netherlands
87 Posts

Posted - 12/06/2002 :  12:31:16  Show Profile  Reply with Quote
in the example, table IP_TestAddress.

So in my database, there a table with IP addresses, and some information about the visitors, First, I wanted to look up every country for every ip when I want information about it. So this takes a lot of time. So in this table, I created an extra column named CountryID so there will be only 1 check up for 1 ip.


Edited by - bjornH on 12/06/2002 12:36:39
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000