RE: D3 and use of compares on indices
Well -- the key is indexed...and so is the date field so the caveat does not apply in this case -- more likely the elusive version 8 is the answer -- Debster -- From Lee Bacall: 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 = ABC 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\ Sel1,-1=cmd * cmd = \SELECT YOURFILE WITH SECONDKEY=SECONDTHINGIE\ Sel1,-1=cmd * *where thirdkey may be a non-indexed element cmd=\SELECT YOURFILE WITH THIRDKEY=YOUGETTHEPICTURE\ Sel1,-1=cmd * *Now, order the list cmd=\SSELECT YOURFILE BY FIRSTKEY BY SECONDKEY BY THIRDKEY\ * max=DCOUNT(Sel1,@VM) FOR z = 1 TO max UNTIL failure EXECUTE Sel1,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 -- u2-users mailing list [EMAIL PROTECTED] http://www.oliver.com/mailman/listinfo/u2-users
Re: D3 and use of compares on indices
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 = ABC 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\ Sel1,-1=cmd * cmd = \SELECT YOURFILE WITH SECONDKEY=SECONDTHINGIE\ Sel1,-1=cmd * *where thirdkey may be a non-indexed element cmd=\SELECT YOURFILE WITH THIRDKEY=YOUGETTHEPICTURE\ Sel1,-1=cmd * *Now, order the list cmd=\SSELECT YOURFILE BY FIRSTKEY BY SECONDKEY BY THIRDKEY\ * max=DCOUNT(Sel1,@VM) FOR z = 1 TO max UNTIL failure EXECUTE Sel1,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
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