>Ah. I see. Well, Fabio, we are certainly defining "optimization" differently, as well as differing in our interpretation of observations. <s>
>
>From my point of view, it goes completely against what was supposed to be special about Fox to activate the appropriate index and say that this is a great optimization on Fox's part. With Rushmore, Fox is supposed to intelligently *figure out* which index to use, based on your expression. If it can only do this when your expression is on the left, this is important.
>
>Why is this important? Beyond the fact that using indexes you do *not* set is what differentiated Fox from other Xbase languages, it allows us to use several indexes at once when we have complex expressions to deal with. Being able to use multiple indexes, none of which are currently active, also allows us to avoid creating and maintaining additional indexes on complex conditions. And it allows us to process data in an order significant to a business rule WITHOUT worrying about optimizing the access. (It also takes a small amount of time to actually switch the order, but this is less significant, unless you have to keep doing it over and over.)
>
>If you have to set the index, you might as well do a SEEK and a WHILE, what is the point of saying that FOR is optimizable? The great advantage that Fox brought to the table was *not* having to set the index.
>
>I am not going to argue with you about the eternal rules of physics, which I know little about <s>, but I will tell you that I think extrapolating from the cases you've provided to "VFP does this or that with Xbase commands" is a bad idea.
>
>In any case, a theoretical or philosophical difference is moot, because the point is to optimize an existing case, and not to make rules in a vacuum. So please try your test again, as below.
>
>You will notice that I have added two different permutations into your code (1) test with the index NOT set to the one you prefer, which is part of what I'm talking about when I discuss Fox's ability to intelligently optimize and (2) test with the expression on the LEFT, because the times are that much faster, and getting things faster is WHAT WE ARE TRYING TO DO <s>.
>
>You should see that, with the index off or with a different index set, it is MUCH faster to put the expression on the LEFT, because Fox will then figure out that it needs to use the inactive index. There are also other SMALL variations that may be seen here as a result of caching, I think, so it would be worthwhile re-running the test differently.
Lisa,
look on the code, 2 errors:
- the divisor is different!
- SET INDEX TO close the idx index
>
>
>>L<
>
>
>GO TOP
>t1=SECONDS()
>COUNT FOR 1=F1
>?? STR(100000*(SECONDS()-t1)/log(reccount()),15,4) !!!!!!!!!!!!!!!!! LOG()
>
>GO TOP
>t1=SECONDS()
>COUNT REST FOR 1=F1
>?? STR(100000*(SECONDS()-t1)/RECCOUNT(),15,4) !!!!!!!!!!!!!!!!!! no LOG
>
Run this, more readable output, with CDX index the control is more simple.
With "=" comparison,
Right and left performance are equal
( except for cache and memory reallocation ),
ACTIVATE SCREEN
CLEAR
CLOSE TABLES all
SET TALK OFF
SET DELETED OFF
SET MEMOWIDTH TO 200
_SCREEN.FontName = "Courier New"
? 'RECCOUNT' AT 40
?? 'R_FULL' AT 55 , 'L_FULL' AT 65
?? 'R_PARTIAL' AT 75 , 'L_PARTIAL' AT 85
?? 'R_NONE' AT 95 , 'L_NONE' AT 105
CREATE CURSOR TEST (F1 I)
FOR J=1 TO 10
DELETE TAG ALL
FOR K=1 TO 100000
INSERT INTO TEST VALUES (0)
NEXT
INSERT INTO TEST VALUES (1)
TEST()
INDEX ON F1 TAG TF1
SET ORDER TO 0
TEST()
SET ORDER TO 1
TEST()
INDEX ON 7 TAG TF2
TEST()
?
NEXT
PROCEDURE test
? "TAGS =",STR(TAGCOUNT(),3)
?? " ORDER SET = "
?? IIF(EMPTY(ORDER()),"NONE" , KEY() + ;
IIF(KEY()=="F1",", SAME", ", DIFFERENT") )
?? RECCOUNT() at 40
GO TOP
t1=SECONDS()
COUNT FOR 1=F1
?? STR(100000*(SECONDS()-t1)/log(reccount()),10,3)
GO TOP
t1=SECONDS()
COUNT FOR F1=1
?? STR(100000*(SECONDS()-t1)/log(reccount()),10,3)
GO TOP
t1=SECONDS()
COUNT REST FOR 1=F1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)
GO TOP
t1=SECONDS()
COUNT REST FOR F1=1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)
GO TOP
t1=SECONDS()
COUNT NOOPTIMIZE FOR 1=F1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)
GO TOP
t1=SECONDS()
COUNT NOOPTIMIZE FOR F1=1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)Now, try this, with "=="
( F1 is a integer, then = and == are equal ? Surprise: No!)
If you read my first message to you, you can found this,
with "==" the right comparison disable the optimization:
The problem is : where is this documented ?
Is this by design, or is this a side effect ?
ACTIVATE SCREEN
CLEAR
CLOSE TABLES all
SET TALK OFF
SET DELETED OFF
SET MEMOWIDTH TO 200
_SCREEN.FontName = "Courier New"
? 'RECCOUNT' AT 40
?? 'R_FULL' AT 55 , 'L_FULL' AT 65
?? 'R_PARTIAL' AT 75 , 'L_PARTIAL' AT 85
?? 'R_NONE' AT 95 , 'L_NONE' AT 105
CREATE CURSOR TEST (F1 I)
FOR J=1 TO 10
DELETE TAG ALL
FOR K=1 TO 100000
INSERT INTO TEST VALUES (0)
NEXT
INSERT INTO TEST VALUES (1)
TEST()
INDEX ON F1 TAG TF1
SET ORDER TO 0
TEST()
SET ORDER TO 1
TEST()
INDEX ON 7 TAG TF2
TEST()
?
NEXT
PROCEDURE test
? "TAGS =",STR(TAGCOUNT(),3)
?? " ORDER SET = "
?? IIF(EMPTY(ORDER()),"NONE" , KEY() + ;
IIF(KEY()=="F1",", SAME", ", DIFFERENT") )
?? RECCOUNT() at 40
GO TOP
t1=SECONDS()
COUNT FOR 1==F1
?? STR(100000*(SECONDS()-t1)/log(reccount()),10,3)
GO TOP
t1=SECONDS()
COUNT FOR F1==1
?? STR(100000*(SECONDS()-t1)/log(reccount()),10,3)
GO TOP
t1=SECONDS()
COUNT REST FOR 1==F1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)
GO TOP
t1=SECONDS()
COUNT REST FOR F1==1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)
GO TOP
t1=SECONDS()
COUNT NOOPTIMIZE FOR 1==F1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)
GO TOP
t1=SECONDS()
COUNT NOOPTIMIZE FOR F1==1
?? STR(100000*(SECONDS()-t1)/RECCOUNT(),10,3)1. The VFP(T) "intelligent" optimization is not the possible best in many cases.
2. Why want you put SQL Engine rules into DBASE Engine ?
Fabio
