Hi Sergey,

Providing the CREATE and INDEX statements is a bit involved as they are built on the fly.

I have managed to get Rushmore to fully optimize with this table in my sample program as follows:

USE PAGE.DBF INDEX PAGEI.CDX ORDER PAGERNAME ALIAS OPENDIR

SELECT PAGERNAME, PAGERTYPE, PAGERATTN, PAGERSTAT, PAGERSTATD, PAGERFWDNU, PAGERFWDNA, PAGERGROUP, PAGERIVR, ATTNUSRDEF FROM OPENDIR INTO ARRAY dirArrayLocal WHERE UPPER(PAGERNAME) = "MYNAMEHERE"

However, with my application, the same (?!) logic does not optimize.
Differences between the two programs that come to mind include:

1. The production app opens many tables.
2. The PA code to do the USE and SQL SELECT is in an in-process COM server
dll.
3. The USE and the SELECT are done in different calls to the dll.
The USE is done during a call to an init method.
The SELECT is done later in another call.


>Hi Howard,
>
>You didn't provide most important piece of information, your table. Can you post CREATE TABLE and INDEX statements that we can run your code?
>When I use table created as follow, I got Full optimization for a query
>

>CREATE TABLE Page ( PAGERNAME  C(20))
>INDEX ON PAGERNAME TAG PAGERNAME
>...
>Using index tag Pagername to rushmore optimize table opendir
>Rushmore optimization level for table opendir: full
>

>
>>I will try this, but meanwhile I have simplified things and am wondering if anything in my process is the problem. Here's some sample code.
>>I've been trying structural and non-structural index files, playing with ordering commands, selecting or not selecting the alias, etc.
>>The only thing that varies is that if DELETED is set ON it will use the _DELETED index key.
>>
>>
>>
>>

>>LOCAL ARRAY dirArrayLocal[1]
>>LOCAL m.showrushmore
>>
>>* Diagnostic to verify SQL optimization.
>>SYS(3054, 1, "showRushmore")
>>
>>SET DEFAULT TO z:\xx\xxdb
>>SET OPTIMIZE ON
>>SET DELETED OFF
>>
>>TRY
>>  *USE PAGE.DBF INDEX PAGEI.CDX ORDER PAGERNAME ALIAS OPENDIR
>>  USE PAGE.DBF ORDER PAGERNAME ALIAS OPENDIR
>>
>>*!*    SELECT OPENDIR
>>*!*    SET ORDER TO PAGERNAME
>>
>>  *SELECT TOP 1 PAGERNAME, PAGERTYPE, PAGERATTN, PAGERSTAT, PAGERSTATD, PAGERFWDNU, PAGERFWDNA, PAGERGROUP, PAGERIVR, ATTNUSRDEF
> FROM OPENDIR INTO ARRAY dirArrayLocal WHERE PAGERNAME = "MYNAMEHERE" ORDER BY PAGERNAME
>>  SELECT TOP 1 PAGERNAME FROM OPENDIR INTO ARRAY dirArrayLocal
>WHERE PAGERNAME = "MYNAMEHERE" ORDER BY PAGERNAME
>>  ? _TALLY
>>  ? showrushmore
>>
>>  UPDATE openDir SET PAGERSTAT = "mystatushere",PAGERSTATD = "",PAGERFWDNU = "",PAGERFWDNU = "",PAGERFWDNA = ""
>WHERE PAGERNAME = "MYNAMEHERE"
>>  ? _TALLY
>>  ? showrushmore
>>
>>CATCH TO oErr
>>    ? ( "Program Error: " + ALLTRIM(STR(oErr.ERRORNO)) ;
>>      + CHR(13) + CHR(10)  + "             " + oErr.MESSAGE ;
>>      + CHR(13) + CHR(10)  + "             at line " + ALLTRIM(STR(oErr.LINENO)) ;
>>      + CHR(13) + CHR(10)  + "             in " + oErr.PROCEDURE )
>>ENDTRY
>>
>>CLOSE DATABASES
>>

>>