FWD » Core Development » Database

ADD TABLE "book" 
  AREA "Schema Area" 
  DUMP-NAME "book" 

ADD FIELD "book-id" OF "book" AS integer 
  FORMAT "->>,>>9" 
  INITIAL "0" 
  POSITION 2
  MAX-WIDTH 4
  ORDER 10

ADD FIELD "author" OF "book" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 3
  MAX-WIDTH 20
  ORDER 20

ADD INDEX "idx1" ON "book" 
  AREA "Schema Area" 
  PRIMARY
  INDEX-FIELD "book-id" ASCENDING 

ADD INDEX "idx2" ON "book" 
  AREA "Schema Area" 
  INDEX-FIELD "author" ASCENDING 

ADD TABLE "book2" 
  AREA "Schema Area" 
  DUMP-NAME "book2" 

ADD FIELD "book-id" OF "book2" AS integer 
  FORMAT "->>,>>9" 
  INITIAL "0" 
  POSITION 2
  MAX-WIDTH 4
  ORDER 10

ADD FIELD "name" OF "book2" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 3
  MAX-WIDTH 20
  ORDER 20

ADD FIELD "stat" OF "book2" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 4
  MAX-WIDTH 20
  ORDER 30

ADD FIELD "author" OF "book2" AS character 
  FORMAT "x(8)" 
  INITIAL "" 
  POSITION 5
  MAX-WIDTH 20
  ORDER 40

ADD INDEX "idx3" ON "book2" 
  AREA "Schema Area" 
  PRIMARY
  INDEX-FIELD "book-id" ASCENDING 

.
PSC
cpstream=ISO8859-1
.
0000001136

DEFINE BUFFER btt1 FOR book.
DEFINE BUFFER btt2 FOR book2.
DEFINE VARIABLE i AS INTEGER NO-UNDO.
DEFINE QUERY q1 FOR btt1, btt2.

FOR EACH btt1:
    DELETE btt1.
END.
FOR EACH btt2:
    DELETE btt2.
END.

DO i = 1 TO 10000:
    CREATE btt1.
    ASSIGN
        btt1.book-id = i
        btt1.author = "foo".
    RELEASE btt1.
END.

DO i = 1 TO 10000:
    CREATE btt2.
    ASSIGN
        btt2.book-id = i
        btt2.name = STRING(i)
        btt2.stat = "progress".
    RELEASE btt2.
END.

def var l1 as datetime.
def var l2 as datetime.
message "starting".
l1 = now.

OPEN QUERY q1 FOR EACH btt1 NO-LOCK WHERE btt1.author = 'foo', FIRST btt2 NO-LOCK WHERE btt2.book-id = btt1.book-id.

MESSAGE NUM-RESULTS ("q1").

l2 = now.
message "NUM-RESULTS" l2 - l1.

l1 = now.
GET FIRST q1.
l2 = now.
message "FIRST" l2 - l1.

l1 = now.
GET NEXT q1.
l2 = now.
message "NEXT" l2 - l1.

CLOSE QUERY q1.