Index and/or algorithm to search two ranges

[Kurt Remlin]

Consider a rather large table where among other fields there are:
VAL1_FROM, VAL1_TO, VAL2_FROM, VAL2_TO.

If I have VAL1 and VAL2, what would be the most efficient way to find
all records for which:
VAL1>=VAL1_FROM .AND. VAL1<=VAL1_TO .AND.;
VAL2>=VAL2_FROM .AND. VAL2 <= VAL2_TO

Clipper 5.2e

[Stephen Quinn]

Kurt

Something like below should work in Clipper 5.2e

FUNCTION Between( _1, _2, _3 )
RETURN (_1 >= _2 .AND. _1 <= _3 )

INDEX ON FLDNAME FOR Between( VAL1, _FIELD->VAL1_FROM , _FIELD->VAL1_TO )
.AND. ;
Between( VAL2 , _FIELD->VAL2_FROM , _FIELD->VAL2_TO )

CYA
Steve

[Kurt Remlin]

Steve,

Are you suggesting building conditional index on the fly for every
VAL1, VAL2 pair I need? If so, isn't it really time consuming?

Am I missing something?

=================================================================
On Jun 19, 7:48 pm, "Stephen Quinn" <stevej...@SPbigpond.AMnet.au>
wrote:

[Stephen Quinn]

Kurt

>Are you suggesting building conditional index on the fly for every
>VAL1, VAL2 pair I need? If so, isn't it really time consuming?

Either that (temp index) or use the condition in a filter.
Depending on which RDD your using (SIXCDX/COMIX) then you can also try
'subindex'

It will also depend (speedwise) on how many records in the DBF for
filtering.

How often does/will the criteria change, if a lot then a filter may be
better, but you'll really need to test it with real data.

CYA
Steve

[N:dlzc D:aol T:com (dlzc)]

Dear Kurt Remlin:

"Kurt Remlin" <pm...@netscape.net> wrote in message
news:058d9934-638e-414b...@y38g2000hsy.googlegroups.com...

>
> Are you suggesting building conditional index
> on the fly for every VAL1, VAL2 pair I need?
> If so, isn't it really time consuming?

Yes, it would be.

You could:
index on str( VAL1_FROM, 9, 0 ) + str( VAL1_TO, 9, 0 ) + ;
str( VAL2_FROM, 9, 0 ) + str( VAL2_TO, 9, 0 )
softseek to str( VAL1, 9, 0 )
"walk" to VAL2 >= VAL2_FROM .and. VAL1 >= VAL1_FROM
then skip and process until either VAL2 > VAL2_TO .or. VAL1 >
VAL1_TO
repeat with the next VAL1, VAL2 pair.

If they are in ascending order, then once you meet the lower
constraint (VAL1 >=VAL1_FROM .and. VAL2 >= VAL2_FROM), you never
need to check those limits. Assuming VALn_FROM <= VALn_TO.

David A. Smith

[N:dlzc D:aol T:com (dlzc)]

"N:dlzc D:aol T:com (dlzc)" <dl...@cox.net> wrote in message
news:_QF6k.1374$W65...@newsfe16.phx...

> Dear Kurt Remlin:
>
> "Kurt Remlin" <pm...@netscape.net> wrote in message
> news:058d9934-638e-414b...@y38g2000hsy.googlegroups.com...
>>
>> Are you suggesting building conditional index
>> on the fly for every VAL1, VAL2 pair I need?
>> If so, isn't it really time consuming?
>
> Yes, it would be.
>
> You could:
> index on str( VAL1_FROM, 9, 0 ) + str( VAL1_TO, 9, 0 ) + ;
> str( VAL2_FROM, 9, 0 ) + str( VAL2_TO, 9, 0 )

Might be better
> index on str( VAL1_FROM, 9, 0 ) + str( VAL2_FROM, 9, 0 ) + ;
> str( VAL1_TO, 9, 0 ) + str( VAL2_TO, 9, 0 )
> softseek to str( VAL1, 9, 0 )+str( VAL1, 9, 0 )

[aardvark]

I think some RDDs provide optimization for filters based on indexes
(Rushmore technology). I've never used them. xHarbour.com sells the
xHarbour equivalents, although you may still be able to find for Clipper.

Another solution you could try:-

1) Create an index on str(VAL1,12,2)+str(VAL2,12,2).
2) Create a Scope using str(VAL1_FROM,12,2)+str(VAL2_FROM ,12,2) and
str(VAL1_TO,12,2)+str(VAL2_TO ,12,2)
3) Create a filter to suppress invalid records within the scope.

IF the VAL2 range is more restrictive than the VAL1 range, it would
obviously come first in the indexing.

Recompiling your application in xHarbour (.com or .org) would allow you
to use ADO. I use the VfpOleDB provider downloaded from Microsoft which
is very fast for this scenario provided you use separate indexes for
VAL1 & VAL2. This solution is fast and "free" if you don't consider
your coding time.

Regards
"aardvark"

[sali]

"Kurt Remlin" <pm...@netscape.net> je napisao u poruci interesnoj
grupi:6b446a74-8d23-4027...@a70g2000hsh.googlegroups.com...

my simple solution is to have primary range "seek-ed", and the secondary
range just "skip-ed". having maintained both indices, val1 and val2, at the
begininig of search to estimate which of them is "primary" and which
"secondary", obviously on the criteria of the min amount of records have to
be checked.