a lack of native support for an IP data type and addresses Dave's
requirement that "everything must happen in the query itself."
As I said, this bit of code was excerpted and adapted from a DNSBL we
run. We found that this chunk of code, when executing it against 150,000
records, was too slow to be used in real time DNS requests. The DNS
requests would take about 5 seconds, which meant that our SMTP banner
would take just as long to appear. There is actually a good deal more
going on, but that's the gist of it.
Our solution was to convert the query to a view. A scheduled task runs
every 15 minutes and populates a table from the view. Now, DNS requests
take milliseconds and the SMTP banner appears almost immediately.
So, for us, performance was an issue, but we did not have similar
requirements to Dave.
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
-----Original Message-----
From: Calvin Ward [mailto:[EMAIL PROTECTED]
Sent: Friday, October 17, 2003 4:35 AM
To: CF-Talk
Subject: Re: Sorting IP Addresses
That sounds like it could perform poorly, would it?
- Calvin
----- Original Message -----
From: Benjamin S. Rogers
To: CF-Talk
Sent: Thursday, October 16, 2003 4:38 PM
Subject: RE: Sorting IP Addresses
> that doesn't work perfectly.
>
> 166.141.22.4
> 166.141.22.40
> 166.141.22.41
> 166.141.22.47
> 166.141.22.48
> 166.141.22.5
Sorry about that. I forgot to convert the substrings to integers. Try
this:
SELECT *
FROM SourceIPSpamCount
ORDER BY
CONVERT(INT, SUBSTRING(SourceIP, 1, CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, SUBSTRING(SourceIP, CHARINDEX('.', SourceIP) +
1,CHARINDEX('.', SourceIP, CHARINDEX('.', SourceIP) + 1) -
CHARINDEX('.', SourceIP) - 1)),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), CHARINDEX('.',
REVERSE(SourceIP)) + 1, CHARINDEX('.', REVERSE(SourceIP),
CHARINDEX('.',
REVERSE(SourceIP)) + 1) - CHARINDEX('.', REVERSE(SourceIP)) - 1))),
CONVERT(INT, REVERSE(SUBSTRING(REVERSE(SourceIP), 1, CHARINDEX('.',
REVERSE(SourceIP)) - 1)))
Benjamin S. Rogers
http://www.c4.net/
v.508.240.0051
f.508.240.0057
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
