Hi all!
We have a query and whant to secure maximum speed. How shall I interpret =
the
Sys(3054,11) report?
Is there more to do?
Sys(3054,11) reports:
Rushmore optimzation level for tblcomp1: none
Rushmore optimzation level for tblaccomps: none
Rushmore optimzation level for tblcont1: none
Rushmore optimzation level for tblacconts: none
Joining tblcomp1 and tblaccomps using index tag Acctocomp
Joining intermediate result and tblcont1 using temp index
Joining intermediate result and tblacconts using temp index
The 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
The tables have following indexes:
tblComp1.C1To234 Primary
tblAccomps.AccToComp Regular
tblCont1.Ct1_c1_234 Regular
tblCont1.Ct1_ct234 Candidate
TblAcconts.AccToCont1 Regular
Why doesn=B4t Rushmore use my indexes? What am I doing wrong? Any help wo=
uld
be very apppreciated.
Regards Roland
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
| |
| |
|
|
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
| |
| |
|
|
----------------------------------------
Subscription maintenance at:
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
Zoran:
I believe you are correct.
Dan Covill
Middleton Consulting, San Diego
At 12:41 03/22/01 +0100, you wrote:
>Hi all,
>
>I need to optimize this SQL query:
>
>SELECT * FROM Gloparams G1 WHERE ;
>cImeVar =3D tcImeVar AND ;
>dVrijediOd =3D ;
>( SELECT MAX( dVrijediOd ) FROM Gloparams G2 ;
> WHERE g2.dVrijediOd <=3D tdDatum) INTO cursor TekVar
>
>For purpose of optimization, I think GloParams table should be indexed with
>
>INDEX ON cImeVar TAG cImeVar
>INDEX ON dVrijediOd TAG dVrijediOd
>
>Am I correct?
>
>Zoran Ljubi=B9i=E6
>powersoft@bj.tel.hr
Hi all,
I need to optimize this SQL query:
SELECT * FROM Gloparams G1 WHERE ;
cImeVar =3D tcImeVar AND ;
dVrijediOd =3D ;
( SELECT MAX( dVrijediOd ) FROM Gloparams G2 ;
WHERE g2.dVrijediOd <=3D tdDatum) INTO cursor TekVar
For purpose of optimization, I think GloParams table should be indexed wi=
th
INDEX ON cImeVar TAG cImeVar
INDEX ON dVrijediOd TAG dVrijediOd
Am I correct?
--
---
Zoran Ljubi=B9i=E6
powersoft@bj.tel.hr
Hello,
I can't seem to get Rushmore to work - all my queries
return 'Rushmore Optimization: none'.
The following is one of the queries I'm trying to
optimize:
SELECT b.a_fund, a.a_fncgrp, a.amount;
FROM force acctdet a left join chart_ac b;
on a.account = b.account;
WHERE a.fb_flag # 'Y';
INTO CURSOR tmp_act1
chart_ac table has approx 50,268 records consisting of
all unique accounts
acctdet table has approx 233,021 records consisting of
multiple records per account.
Thanks in advance,
Inna
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
Inna,
You do have an index on fb_flag, don't you? Rushmore can only work if you
have an index built on the expression listed in your WHERE clause.
Also, while Rushmore doesn't do JOINs, you can further optimize your query
by ensuring you have indexes on your account column in both tables.
OH! Why do you have the FORCE clause in this query? That changes things...
-Chuck Urwiler, MCT, MCSD, MCDBA
Sr. Instructor/Consultant
http://www.hentzenwerke.com/catalogavailability/csvfp.htm
-----Original Message-----
From: Inna :) [mailto:inulia25@yahoo.com]
Sent: Tuesday, July 31, 2001 11:48 AM
To: Multiple recipients of ProFox
Subject: Rushmore Question
Hello,
I can't seem to get Rushmore to work - all my queries
return 'Rushmore Optimization: none'.
The following is one of the queries I'm trying to
optimize:
SELECT b.a_fund, a.a_fncgrp, a.amount;
FROM force acctdet a left join chart_ac b;
on a.account = b.account;
WHERE a.fb_flag # 'Y';
INTO CURSOR tmp_act1
chart_ac table has approx 50,268 records consisting of
all unique accounts
acctdet table has approx 233,021 records consisting of
multiple records per account.
Thanks in advance,
Inna
Remove the FORCE keyword. It tells Rushmore not to kick in.
Also, you'd be well advised not to use a and b as local aliases. They can
cause problems. Avoid the letters a-j and m because of their historical use
as work area designators.
Tamar
-----Original Message-----
From: profox@leafe.com [mailto:profox@leafe.com]On Behalf Of Inna :)
Sent: Tuesday, July 31, 2001 11:48 AM
To: Multiple recipients of ProFox
Subject: Rushmore Question
Hello,
I can't seem to get Rushmore to work - all my queries
return 'Rushmore Optimization: none'.
The following is one of the queries I'm trying to
optimize:
SELECT b.a_fund, a.a_fncgrp, a.amount;
FROM force acctdet a left join chart_ac b;
on a.account = b.account;
WHERE a.fb_flag # 'Y';
INTO CURSOR tmp_act1
chart_ac table has approx 50,268 records consisting of
all unique accounts
acctdet table has approx 233,021 records consisting of
multiple records per account.
Thanks in advance,
Inna
__________________________________________________
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
Clark,
I'm not certain, but I would think the only good reason to turn off
Rushmore optimization is if you are working with really large tables and
writing complex queries against them. I say this because one of the
features of Rushmore is to build an in-memory bitmap of matching
records. I could see on large tables where the bitmap(s) would require
too much memory, requiring VFP to do lots of extra work just to make
room for the bitmaps.
Another idea that comes to mind is if you have non-discriminating
indexes. In this case, Rushmore would choose to use the indexes but they
would be more of a burden than a help. However, a better choice would be
to drop these indexes instead.=20
Just ideas from the top of my head. I've never seen a reason to turn it
off in my experience.=20
-Chuck Urwiler, MCT, MCSD, MCDBA
http://www.eps-software.com=20
-----Original Message-----
From: White, Clark [mailto:WhiteC@jsc.mil]=20
Sent: Friday, September 13, 2002 9:23 AM
To: Multiple recipients of ProFox
Subject: VFP6: Rushmore/Network Performance
I have found a lot of interesting information on removing the deleted()
index tags to improve performance on a network.
Has anyone on the list ever tried using the NOOPTIMIZE clause to turn
Rushmore off to increase performance? If so, does this help at all?
Clark