>Hello Thomas, Hello Aleksey
>
>Yes, I was the originator of idea1 and 2
>
>>idea 1: spell out the alias of the intermediate file - benefits:
>>idea 2: give some info on the key expression of the temporary index *and* the alias it is created on.
>>idea 3: whenever vfp changes join order print out a line with the reason.
>
>There's also a rather old wish on the wish list from Thomas (ID:742), which also had the idea 3 and the idea to have a measurement of the time each single step would take.
>
>Another thing, that's irritating on the output of sys(3054) is, that you get "none" optimization messages for filter conditions on tables, that have no filter condition in the sql. This leads to the belief, that rushmore fails to fully optimize.
>
>for example: Select * from tabParent P inner join tabChild C on c.id=p.id where c.somefield=someexpression
>will result in a line "Rushmore optimization level for table tabParent: none", because there is no where clause regarding tabParent. Reads, as if the sql is not fully optimized, but if there is an index on id surely the join is optimized. I'd skip these irritationg messages, just show what's really done.
>


First: If set deleted is on, then optimization step is done.
Second: Aleksey can add a bit to the sys() parameter to return optimization only.

But the contrary is true,
and I think that Aleksey should end the standard behavior,
before making new things

look this on VFP9:

CREATE CURSOR cc (ff i)
DIMENSION AX[1000000,1]
APPEND FROM ARRAY AX
INSERT INTO cc VALUES (2)

SYS(3054,11)

INDEX ON FF TAG TFF1

CLEAR

SET DELETED OFF

T1=SECONDS()
? "this uses TFF1 and is optimized"
SELECT MAX(FF) FROM ALIAS() INTO ARRAY AX
? SECONDS()-T1

SET DELETED ON
T1=SECONDS()
? "this don't uses TFF1 and is not optimized"
SELECT MAX(FF) FROM ALIAS() INTO ARRAY AX
? SECONDS()-T1

INDEX ON FF FOR NOT DELETED() TAG TFF2

T1=SECONDS()
? "this uses TFF2 and is optimized"
SELECT MAX(FF) FROM ALIAS() INTO ARRAY AX
? SECONDS()-T1

USE

for COUNT(ff) the work is partial

CREATE CURSOR cc (ff i)
DIMENSION AX[1000000,1]
APPEND FROM ARRAY AX
INSERT INTO cc VALUES (2)

SYS(3054,11)

INDEX ON FF TAG TFF1

CLEAR

SET DELETED OFF

T1=SECONDS()
? "BAD: this uses TFF1 and is optimized"
SELECT count(FF) FROM ALIAS() INTO ARRAY AX
? SECONDS()-T1

SET DELETED ON
T1=SECONDS()
? "GOOD: this don't uses TFF1 and is not optimized"
SELECT count(FF) FROM ALIAS() INTO ARRAY AX
? SECONDS()-T1

INDEX ON FF FOR NOT DELETED() TAG TFF2

T1=SECONDS()
? "GOOD: this uses TFF2 and is optimized"
SELECT count(FF) FROM ALIAS() INTO ARRAY AX
? SECONDS()-T1

USE

>How about ASHOWPLAN(ArrayName,nOption[,cSQL]), a new array function creating a showlplan array, that can be analized programmatically. Each line of the array would then show one step of the sql execution, one filter or one join:
>
>1: cAlias1 (1. alias involved)
>2: cAlias2 (2. alias involved (if at all))
>3: cClause (Where or Join/On clause)
>4: cTag (tag of the index used or (virtual) tag name of a temp index created)
>5: cKeyexpression (index expression, perhaps with ON cAlias1/2, that is on which alias, if temp index)
>6: cOptimizationType (filter/join)
>7: cOptimizationsuccess (none, partial, full)
>8: cResultAlias (cursor alias of sql,table alias of sql or (virtual) intermediate result alias, could also be cAlias1 for where clauses)
>9: nMilliseconds (time of this single step)
>10: cReasonOrderChange (reason for order change, if order changed compared to the original sql/FORCE)
>11: nSubquerylevel (level of the subquery executed, 1 for main select or parallel union selects)
>12: nUnionlevel (number of the union select executed at the moment, 1...n)
>13: cPartialSQL (an sql statement equivalent to the executed single step, if possible - I hope you are not superstitious)
>
>Bye, Olaf.