RE: D3 and use of compares on indices

2004-02-17 Thread astarte00
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

2004-02-15 Thread 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


D3 and use of compares on indices

2004-02-13 Thread astarte00


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