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