IPv4 addresses are 32-bit numbers expressed for human convenience as 4
numbers between 0 and 255 separated by dots, but they can really be
handled more easily as a single 32-bit integer.
So, have you added a column to your table that stores the integer
value of the IP address?
ALTER TABLE YourTable ADD COLUMN dIPADDR INT(16)
UPDATE YourTable SET dIPADDR = ipsegment[1]*256^3 +ipsegment[2]*256*2
+ ipsegment[3]*256 + ipsegment[4]
Then add an index on that column.
Convert your search value using the same algorithm.
An answer on a 50,000 row table should be returned in less than one second.
On Fri, Dec 15, 2023 at 6:08 PM Paul H. Tarver <p...@tpcqpc.com> wrote:
>
> Been quiet in here for a while, so I thought I'd toss out something I'm
> tinkering with and ask for your advice. Besides, I want to keep my name on
> the monthly top 10! J
>
>
>
> I have a large table populated with the IP2LOCATION-LITE-DB3 list. I'm
> running a process to convert an IPV4 address into a decimal number and then
> locate the first record in my table where the value I'm looking up is
> between the From_IP numeric value and the To_IP numeric value in the table
>
>
>
> Here's the code snippet I have working now:
>
> ----------------------------------------------------------------------
>
> LPARAMETERS toParams
>
> LOCAL lcBase2IP, lnElements, lnIpNumber
>
>
>
> IF USED("ip_locs")
>
>
>
> lcCurrSel = ALIAS()
>
>
>
> lnElements = ALINES(ipsegment, toParams.ocClientIp, 1, ".")
>
>
>
> IF lnElements = 4
>
>
>
> lcBase2IP =
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[1])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[2])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[3])), 2), 8, "0") + ;
>
>
> PADL(THISFORM.utilities.DEC2BASX(VAL(ALLTRIM(ipsegment[4])), 2), 8, "0")
>
>
>
> lnIpNumber = THISFORM.utilities.bin2dec(lcBase2IP)
>
>
>
> SELECT ip_locs
>
> LOCATE FOR lnIpNumber => ip_locs.ip_from AND lnIpNumber <=
> ip_locs.ip_to
>
>
>
> IF FOUND('ip_locs')
>
>
>
> toParams.ocCountry_Code = ip_locs.Country_Code
>
> toParams.ocCountry_Name = ip_locs.Country_Name
>
> toParams.ocRegion_Name = ip_locs.Region_Name
>
> toParams.ocCity_Name = ip_locs.City_Name
>
>
>
> ENDIF
>
>
>
> ENDIF
>
>
>
> SELECT (lcCurrSel)
>
>
>
> ENDIF
>
>
>
> RETURN
>
> -----------------------------------------------------------
>
>
>
> This works fine and it really isn't that slow. It takes about 30 - 60
> seconds to do the location look up on a list of 50,000+ ip address records.
> I'm pretty sure I'm getting Rushmore optimization on the LOCATE FOR line,
> but I've been wondering if there is a way to use SEEK with an index to find
> a value between the numeric IP_FROM field and the numeric IP_TO field. But I
> cannot figure out how I can create an index that would give me Rushmore
> optimization and let me use the SEEK command for a value between those to
> numerics.
>
>
>
> FYI, I tried doing a simple SQL query to a temporary cursor, but
> surprisingly, the LOCATE FOR command is actually faster. Hey, I know I'm
> being greedy, but I'm wondering if it is possible to squeeze any more speed
> out of this process. I may be using the best method, but I figured if y'all
> had a minute you might give me a little feedback.
>
>
>
> Thanks in advance!
>
>
>
> Paul H. Tarver
> Tarver Program Consultants, Inc.
>
>
>
>
>
>
>
> --- StripMime Report -- processed MIME parts ---
> multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: https://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: https://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: https://leafe.com/archives
This message:
https://leafe.com/archives/byMID/CACW6n4vBBNaoWjARfLMGBGoVe96FEixm_UE_X-roNmu4s=m...@mail.gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
