A bit of a caveat in making selects against indexed keys may be helpful...
If one is doing a select against a file with an indexed key, as:
SELECT YOURFILE WITH YOURKEY = "YOURREQUEST"
will build a select list against only those records containing the key data
"YOURREQUEST"
If, on the other hand if one is doing a select against a file (even when all
requested criteria are indexed) as:
SSELECT YOURFILE WITH FIRSTKEY = "YOURREQUEST" AND WITH SECONDKEY =
"OTHERSTUFF" AND WITH THIRDKEY = "A""B""C" BY FIRSTKEY BY SECONDKEY BY
THIRDKEY
The entire file (at least in D3 and jBASE) will be stepped through /
searched, WITHOUT the benefit of using keys.
Here is some methodology that we have developed to:
1. clarify the building and maintenance of select statements
2. ensure that keys are used to reduce system resource requirements
EQU TRUE TO 1, FALSE TO 0
Sel = ""
failure = FALSE
cmd = \SELECT YOURFILE WITH FIRSTKEY = "FIRSTTHINGIE"\
Sel<1,-1>=cmd
*
cmd = \SELECT YOURFILE WITH SECONDKEY="SECONDTHINGIE"\
Sel<1,-1>=cmd
*
*where thirdkey may be a non-indexed element
cmd=\SELECT YOURFILE WITH THIRDKEY="YOUGETTHEPICTURE"\
Sel<1,-1>=cmd
*
*Now, order the list
cmd=\SSELECT YOURFILE BY FIRSTKEY BY SECONDKEY BY THIRDKEY\
*
max=DCOUNT(Sel<1>,@VM)
FOR z = 1 TO max UNTIL failure
EXECUTE Sel<1,z> CAPTURING messages
failure=NOT(SYSTEM(11))
NEXT z
IF NOT(failure) THEN
GOSUB DoYourThing ;! not shown
END
STOP.
This will ensure that btree keys will be used to make the initial list
selections.
The non-indexed keys would then be used in secondary tiers of the select
procedure to reduce the impact of having to step through the entire file.
After the selections are made, the list can then be ordered with greatly
reduced impact on the system.
Any comments?
Lee Bacall
http://www.binarystar.com
Phone: +1 (954) 791-8575, emergency# 954-937-8989
----- Original Message -----
From: <[EMAIL PROTECTED]>
To: "U2 Users Discussion List" <[EMAIL PROTECTED]>
Sent: Friday, February 13, 2004 1:49 PM
Subject: D3 and use of compares on indices
>
>
> On D3 VERION 7.3 what is the syntax to use compares other than = if you
are using an indexed dictionary in the statement. It seems to return the
correct items if it is simply looking for an equal condition but anything
other, such as > < etc. It appears that it does not use the index, but
rather scans the entire file when these operators are used. This becomes a
problem especially when a date field has been indexed since that is the most
common application.
>
> Has anyone else experienced this problem? This is D3 on W2k
--
u2-users mailing list
[EMAIL PROTECTED]
http://www.oliver.com/mailman/listinfo/u2-users
