Cecil
19+ years progress programming and still learning.
OE 11.3 WebSpeed
OS Linux CentOS 6.4 64bit
I've been a Progress Programmer for 15-16 years quite happily writing code the and learning from other Progress developers along the way. However for the last 6-7 years I been coding solo and I came across a complexing situation with a new piece of code I'm writing.
I've created simple database which stores Geo IP Address ranges relating IP address to Geo Location i.e. City & Country. I know there are API Web Services which does for me, I just wanted to create my own Database.
The confusion I am having is between FOR FIRST vs. FOR LAST for a unique record find, when there are about 1.7 million records.
This query takes 0-1 milliseconds to process.
This code takes about 4752 milliseconds to process.???
Why would FOR FIRST Take longer to process? I must confess that I hardly (almost never) use a FIND LAST or FOR LAST statement because I've never needed to. Teach an Old dog new trick.
The convIPaddressToDec function just convert IPv4 IP address into a integer (int64).
DataDefinitions for the GeoCityBlock Table:
OS Linux CentOS 6.4 64bit
I've been a Progress Programmer for 15-16 years quite happily writing code the and learning from other Progress developers along the way. However for the last 6-7 years I been coding solo and I came across a complexing situation with a new piece of code I'm writing.
I've created simple database which stores Geo IP Address ranges relating IP address to Geo Location i.e. City & Country. I know there are API Web Services which does for me, I just wanted to create my own Database.
The confusion I am having is between FOR FIRST vs. FOR LAST for a unique record find, when there are about 1.7 million records.
This query takes 0-1 milliseconds to process.
Code:
DEFINE VARIABLE inIPAddress AS INT64 NO-UNDO.
inIPAddress = convIPaddressToDec(INPUT "114.45.67.89").
FOR LAST GeoCityBlock NO-LOCK
WHERE GeoCityBlock.IPAddressFromDec LE inIPAddress
AND GeoCityBlock.IPAddressToDec GE inIPAddress:
END.
This code takes about 4752 milliseconds to process.???
Code:
DEFINE VARIABLE inIPAddress AS INT64 NO-UNDO.
inIPAddress = convIPaddressToDec(INPUT "114.45.67.89").
FOR FIRST GeoCityBlock NO-LOCK
WHERE GeoCityBlock.IPAddressFromDec LE inIPAddress
AND GeoCityBlock.IPAddressToDec GE inIPAddress:
END.
Why would FOR FIRST Take longer to process? I must confess that I hardly (almost never) use a FIND LAST or FOR LAST statement because I've never needed to. Teach an Old dog new trick.
The convIPaddressToDec function just convert IPv4 IP address into a integer (int64).
DataDefinitions for the GeoCityBlock Table:
Code:
ADD TABLE "GeoCityBlock"
AREA "Schema Area"
DUMP-NAME "geocity"
ADD FIELD "IPAddressFromDec" OF "GeoCityBlock" AS int64
FORMAT ">>>>>>>>>9"
INITIAL "0"
LABEL "IP Address From Dec"
POSITION 2
MAX-WIDTH 8
COLUMN-LABEL "IP AddressFrom Dec"
ORDER 30
ADD FIELD "IPAddressToDec" OF "GeoCityBlock" AS int64
FORMAT ">>>>>>>>>9"
INITIAL "0"
LABEL "IP Address To Dec"
POSITION 3
MAX-WIDTH 8
COLUMN-LABEL "IP Address To Dec"
ORDER 40
ADD FIELD "LocationID" OF "GeoCityBlock" AS integer
FORMAT ">>>>>>9"
INITIAL "0"
LABEL "LocationID"
POSITION 4
MAX-WIDTH 4
COLUMN-LABEL "LocationID"
ORDER 50
ADD INDEX "idxIPAddressRange" ON "GeoCityBlock"
AREA "Index Area"
PRIMARY
INDEX-FIELD "IPAddressFromDec" ASCENDING
INDEX-FIELD "IPAddressToDec" ASCENDING
.
PSC
cpstream=UTF-8
.
0000000840
Last edited: