Re: Index & Seek Question

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 paul@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]