MauditOstie
Member
Hello Ptalkers,
Well, after 1 day of testing, i was able to figure out some pretty weird things and found out the reason why i have a bad browser reposition! Seems an index is not behaving well!!
First, here's the indexes definition:
Then, here are the testing i did:
I don't get it! Why is that index running so slowly if i can scan the whole table within 15sec?
This is also causing my REPOSITION command to take up to 3 minutes in my browser! Anyone have an idea as of why the index i-effective is so slow?
Well, after 1 day of testing, i was able to figure out some pretty weird things and found out the reason why i have a bad browser reposition! Seems an index is not behaving well!!
First, here's the indexes definition:
Code:
ADD INDEX "i-Primary" ON "emplr-status"
AREA "Schema Area"
UNIQUE
PRIMARY
DESCRIPTION "Primary Index"
INDEX-FIELD "emplr-code" ASCENDING
INDEX-FIELD "seq-no" ASCENDING
INDEX-FIELD "Effective-Date" ASCENDING
*** This index is a test i created this morning ****/
ADD INDEX "i-effective" ON "nss-emplr-status"
AREA "Schema Area"
INDEX-FIELD "emplr-code" ASCENDING
INDEX-FIELD "Effective-Date" ASCENDING
ADD INDEX "i-Status" ON "nss-emplr-status"
AREA "Schema Area"
INDEX-FIELD "Status-Code" ASCENDING
INDEX-FIELD "Effective-Date" ASCENDING
Then, here are the testing i did:
Code:
DEFINE TEMP-TABLE tt LIKE nss-emplr-status
FIELD rID AS ROWID.
FOR EACH nss-emplr-status NO-LOCK
WHERE nss-emplr-status.status-code <= 4
AND nss-emplr-status.effective-date <= TODAY:
FIND tt WHERE tt.emplr-code = nss-emplr-status.emplr-code NO-LOCK NO-ERROR.
IF NOT AVAIL(tt) THEN
CREATE tt.
ELSE IF tt.seq-no > nss-emplr-status.seq-no THEN NEXT.
BUFFER-COPY nss-emplr-status TO tt.
ASSIGN tt.rID = ROWID(nss-emplr-status).
END.
/***** This code takes arround 15 secondes to run *****/
/***** So this is 200 000 records in arround 15 secondes *****/
/***** If i then do: *****/
FOR EACH tt EXCLUSIVE-LOCK:
DELETE tt.
END.
/***** This takes 5 seconds for 100 000 records ******/
/***** BUT! IF I DO THIS: *****/
FOR EACH tt EXCLUSIVE-LOCK:
FIND LAST nss-emplr-status WHERE
nss-emplr-status.emplr-code = tt.SSR AND
nss-emplr-status.effective-date <= TODAY
NO-LOCK NO-ERROR.
IF AVAIL(nss-emplr-status)AND
nss-emplr-status.status-code = 5 THEN
DELETE tt.
END.
/***** This takes 2min32 seconds on top of the 15 it took before!?!?!?!?!? ****/
I don't get it! Why is that index running so slowly if i can scan the whole table within 15sec?
This is also causing my REPOSITION command to take up to 3 minutes in my browser! Anyone have an idea as of why the index i-effective is so slow?