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 ---