Organization: The Software Sweatshop, Cleveland, Ohio USA
Newsgroups: bit.listserv.dbase-l
Date:         Fri, 20 Nov 1992 21:57:12 EST
Reply-To:     "Mark W. Schumann" <mark@whizbang.wariat.org>
              dialects" <DBASE-L@NMSUVM1.BITNET>
From:         "Mark W. Schumann" <mark@WHIZBANG.WARIAT.ORG>
Subject:      Re: Hypothetical Situation (Still Indexing Problem)

Rich Gautier wrote:

> Let's say I
>
> USE DATABASE INDEX DATE, SRAN, SOURCE, ORG
> And I
>
> SEEK "9210"
> which is a DATE value

Is not.  That's a CHARACTER value.  It might be a character value that
looks like a date... but this is not really your point.  Go ahead.  :-)


> then I do a SET INDEX to SRAN
> Will the record pointer STAY on the record I just SEEKed, allowing me to
> skip through the SRAN's for that DATE?

To change the active index, use SET ORDER TO ... instead.

//-------------------- cut here --------------------
USE database INDEX date, sran, source, org
SEEK "9210"
SET ORDER TO 2   // Activates the SRAN index.
//-------------------- cut here --------------------

If you SET INDEX TO you will close all other active indexes.  Then if
you update anybody's key value, you will have incorrect indexes.



> Remember, what I am trying to do is to get to any record based on ANY
> combination of the SIX fields, even if I only know one or two of them.  I will
> merely be seeking for whichever ones I DO know.

Now suppose you are looking for a combination of DATE and SRAN, and you
can't really have a DATE+SRAN index because of all the permutations.
Your user or calling program passes a DATE and SRAN in the function's
parameters.

//-------------------- cut here --------------------
FUNCTION lookup (cpDate, cpSran)

USE database INDEX date, sran, source, org
dbseek (cpDate, .T.)  // This does a "soft" seek.

// Start at current record, look for match.
// Give up when you run out of matching dates.
LOCATE FOR SRAN == cpSran WHILE DATE == cpDate

RETURN (SRAN == cpSran .and. DATE == cpDate)  // .TRUE. if match found
//-------------------- cut here --------------------


Now obviously this example is a little bare; you would want to use
variables for the field names, and then by the way there is a good way
to avoid macros by using FIELDBLOCK().  But the point is 1) you can't
SEEK on the first key and then SEEK on the second key, because the
latter just ignores the former (you knew this!); and 2) your strategy
of switching index orders doesn't solve the problem because as you
are skipping through in SRAN order there is absolutely no guarantee
that your DATE values will be what you want.

To illustrate point #2, imagine this is your database and step through
both your example and mine.

DATE    SRAN
9210     11
4992      1
7781      2
4329      3
8943      4
2112      5
9812      6
3321      7
1012      8
1003      9
9210     10

Now find (DATE == "9210" .and. SRAN == 10).  Did your technique force
you to read the whole file?  Did it even get the right answer?

Then try my version.  You should have read only the first and last
(physical order) records in the file.  The reason for the inefficiency
in your original plan is that the SEEK to "9210" doesn't necessary bring
you anywhere near the right SRAN value; you could have even overshot.

Does this look complicated?  You bet.  *THIS* technique, together with
similar optimizations, *IS* the Rushmore Technology.  Rushmore is not an
index type, nor is it magic.  It's a chunk of run-time code in FoxPro
that automatically figures out the most efficient way to use existing
indexes in a query of the sort you describe.

Rushmore is handy for interactive queries--which is what you are trying
to do--but most application developers can know ahead of time what
lookups need to be done on their database files and can arrange for an
optimized lookup the old-fashioned way with little effort.

That's why the Rushmore test results look so good but are not very
relevant to what many application developers really do.  In your case,
OTOH, it may be just the ticket.

Many users need a few custom reports and would not benefit from all this
flexibility.  The ones who do need it may be better served if you just
use a third-party tool such as Grumpfish Reporter or R & R Report
Writer.

Your call, Rich.  :-)
--
--
Mark W. Schumann/3111 Mapledale Avenue/Cleveland, Ohio 44109-2447 USA
Domain: mark@whizbang.wariat.org                      CIS: 73750,3527
"We're not nuts or anything; I mean *I'm* not nuts." <whizbang!judy>
Comments: Gated by NETNEWS@AUVM.AMERICAN.EDU