Author: Cindy Winegarden
Posted: 2000-09-03 at 19:24:46
Roland and Christina,
There was an excellent article in FoxPro Advisor by Chris Probst in May,
1999.
Basically what Chris said was that FoxPro copies each index referenced in
your query to the local machine, reads the index, decides which records t=
o
retrieve, gets the records, and then locally sorts through and throws out
any records which don't totally fit the query (values which did not have =
an
index.) What he found was that for fields which had only a small number =
of
discrete values like DELETED() or Male/Female, or Adult/Child or
Homeowner/Renter that it was often more time consuming for FoxPro to pull
the whole index across and read it, than it would be if there were no ind=
ex,
a few unwanted records were retrieved and then say 10 out of 50 records w=
ere
retrieved but discarded locally.
I see that some of your indexes are Primary or Candidate and one can assu=
me
all of those values are unique and should be indexed for optimum
performance. Your other "Regular" indexed fields should be evaluated
against the above advice.
One way to evaluate the part the network plays in your situation is to co=
py
all of the tables to some machine and run the query several times locally
and record the time it takes. (Use lnBeginSeconds =3D SECONDS() before t=
he
query and take a similar reading of the time after the query.) You must
restart the machine between tests since FoxPro actually creates indexes
locally for fields which aren't indexed and these will persist if the tes=
t
is repeated without the restart.
*** But your original post said that Rushmore was not being used at all. =
***
Are the indexes on the exact same expressions as the fields? (Chapter 1=
5
in Programmer's Guide). Are the fields the same size (not C(10) and C(13=
)
or something like that)
The Help also says: "You can globally disable or enable Rushmore for all
commands that benefit from Rushmore, with the SET OPTIMIZE command." I
guess you could check the setting of SET("Optimize") when you run your
query.
That's everything that I can think of or know of. Where's Anders?
--
Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro
Duke Children's Information Systems
Duke University Medical Center
cindyw@duke.edu
----- Original Message -----
From: "Roland Nyberg" <nyberg.roland@telia.com>
To: "Multiple recipients of ProFox" <profox@leafe.com>
Sent: Sunday, September 03, 2000 4:56 PM
Subject: SV: Rushmore - VFP6/SP4
Hi Cindy!
I now realize you haven=B4t seen Christina=B4s answer. Here it comes agai=
n:
Thank=B4s for your comment.
1 TblAcconts.AccToCont1 has a regular index set.
2 I am not satisfied with the speed.
We have now tried to set index on deleted. No substantial improvement...
Regards
Roland
-----Ursprungligt meddelande-----
Fr=E5n: profox@leafe.com [mailto:profox@leafe.com]F=F6r Cindy Winegarden
Skickat: den 31 augusti 2000 17:26
Till: Multiple recipients of ProFox
=C4mne: Fw: Rushmore - VFP6/SP4
Roland,
I posted this on Monday. Did you or Christina try it?
Cindy Winegarden
Microsoft Certified Professional, Visual FoxPro
Duke Children's Information Systems
Duke University Medical Center
cindyw@duke.edu
----- Original Message -----
From: "Cindy Winegarden" <cindyw@duke.edu>
Newsgroups: microsoft.public.fox.programmer.exchange
Sent: Monday, August 28, 2000 5:54 PM
Subject: Re: Rushmore - VFP6/SP4
| Christina,
|
| I don's see mention of an index on TblAcconts.AccToCont1.
|
| Also, Rushmore will not report full optimization unless there is a tag =
on
| DELETED(). However, a tag on ANY field where there are only a few valu=
es
| (M/F, Yes/No) may or may not speed things up, depending a lot on your
| particular situation. There was an excellent article in FoxPro Advisor
| about this.
|
| What you didn't say was whether you were satisfied with the speed of yo=
ur
| query!
|
| (Surely you go nuts with table and field names which are so similar!
;-) )
|
|
| --
|
|
| Cindy Winegarden
| Microsoft Certified Professional, Visual FoxPro
|
| Duke Children's Information Systems
| Duke University Medical Center
| cindyw@duke.edu
|
|
|
| "Christina Lofberg" <ch.lofberg@telia.com> wrote in message
| news:p6wq5.4340$HK.198022@newsc.telia.net...
| | Hi all!
| | I have a query:
| |
| | Select tblComp1.Company, tblCont1.aftername, tblCont1.forename,
| | TblCont1.ct1_ct234, ;
| | TblComp1.c1to234, tblComp1.Det014,tblComp1.Det015,tblCont1.Det014 as
| | ContDet014 ;
| | FROM tblComp1 ;
| | LEFT JOIN tblAccomps ON tblComp1.C1To234 =3D tblAccomps.AccToComp ;
| | LEFT JOIN tblCont1 ON tblComp1.C1To234 =3D tblCont1.Ct1_c1_234 ;
| | LEFT JOIN tblAcconts ON tblCont1.Ct1_ct234 =3D TblAcconts.AccToCont1
| |
| | Sys(3054,11) reports no indexes or only temp indexes are used.
| |
| | The tables have following indexes:
| | tblComp1.C1To234 Primary
| | tblAccomps.AccToComp Regular
| | tblCont1.Ct1_c1_234 Regular
| | tblCont1.Ct1_ct234 Candidate
| |
| | Why doesn=B4t Rushmore use my indexes? What am I doing wrong? Any hel=
p
would
| | be very apppreciated.
| |
| | Regards
| | Christina