Dan,

How are you testing? VFP caches index data between runs. The only real test is to turn the machine off between tests. Also, eliminate network issues by testing with data on a your hard drive.


>Cindy,
>
>That's very help info.
>
>It shows that it has to create a temporary index even though it is fully optimized. I thought that this was a problem.
>
>But in one test I got times from an average of 13 seconds when not creating the index to 15 seconds when creating the index.
>
>>Dan,
>>
>>You've talked about the optimization level, but you haven't talked about the percieved speed of your code.
>>
>>Indexing on DELETED() may make the optimization show as "Fully optimized" but it may actually be slower than not having an index on DELETED(). In general, you are better off NOT indexing on fields which have only a few discrete values, such as DELETED or gender. There was a FoxPro Advisor article about this which explained exactly why.
>>
>>My impression is that your indexing strategy is correct, as far as it goes. You might, however, increase speed by causing VFP to make a temporary table on the first pass by doing:

>>Select *, <b>.F. AS AddedField</b> ;
>>    From Invoice ;
>>    Where ;
>>        InvoiceId = 3 And ;
>>        Between( InvoiceDate, {12/01/01}, {12/31/01} ) ;
>>    Into Cursor qInvoices
>>
>>*!* You may not need to index qInvoices
>>*!* Since I think you only need to index the child table. Test to be sure.
>>*!* Index On InvoiceId Tag InvoiceId
>>
>>Select ;
>>       qInvoices.*, ;
>>       InvoiceDetail.Amount ;
>>    From qInvoices ;
>>        Inner Join InvoiceDetail On ;
>>        qInvoices.InvoiceId = InvoiceDetail.InvoiceId ;
>>    Into Table InvoiceAmounts

>>
>>
>>>Sorry hit submit by mistake
>>>
>>>>Here is sample code to illustrate the point.
>>>

>>>Sys(3054,11)
>>>
>>>Select * ;
>>>    From Invoice ;
>>>    Where ;
>>>        InvoiceId = 3 And ;
>>>        Between( InvoiceDate, {12/01/01}, {12/31/01} ) ;
>>>    Into Cursor qroInvoices
>>>
>>>Use Dbf("qroInvoices") Again In 0 Alias qInvoices
>>>Select qInvoices
>>>Index On InvoiceId Tag InvoiceId
>>>
>>>Select ;
>>>       qInvoices.*, ;
>>>       InvoiceDetail.Amount ;
>>>    From qInvoices ;
>>>        Inner Join InvoiceDetail On ;
>>>        qInvoices.InvoiceId = InvoiceDetail.InvoiceId ;
>>>    Into Table InvoiceAmounts
>>>

>>>
>>>I have an index on InvoiceDate I have an index on InvoiceId in both the Invoice and InvoiceDetail. I have an index on Deleted() in both the Invoice and InvoiceDetail.
>>>
>>>Everything shows as fully optimized but one line which states:
>>>Rushmore Optimization level for intermediate result: None
>>>
>>>What do I need to do to get full optimization for the intermediate result?
>>>
>>>TIA