Re: Index & Seek Question

If the field is indexed you can use SET SOFTSEEK. I think you'd then have to do another check to ensure the value being pointed to is also less than the upper bound.

HTH, -Charlie

On 12/15/2023 5:08 PM, Paul H. Tarver 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]