>>The following query take about 12 minutes to run on my machine. Anyone have any suggestion on how to improve performance?
>>
>>This is running on: Pentium 100MHz, 32MB RAM with VFP5.0a. Database on local hard disk, single user
>>
>>Supplier:
>>- 379 records (DBF: 64KB, FPT: 25KB)
>>- Index on iKey
>>- Index on Deleted()
>>
>>ProductList:
>>- 19 055 records (DBF: 689KB, FPT: 1 192KB)
>>- Index on iKey
>>- Index on iKeySupplier
>>- Index on Deleted()
>>
>>MemberData:
>>- 115 837 records (8 033KB)
>>- Index on iKeyMember
>>- Index on iKeyProduct
>>- Index on Deleted()
>>
>>MemberList:
>>- 172 records (10KB)
>>- Index on iKey
>>- Index on Deleted()
>>
>>SET ANSI OFF
>>SET DELETED ON
>>
>>SELECT MemberData.iKey, PadR(MemberList.cName, 50) AS cMember,;
>> PadR(Supplier.cName, 50) AS cSupplier, PadR(ProductList.cCatalogNo, 20) AS cCatalogNo,;
>> PadR(MLine(ProductList.mName, 1), 50) AS cProduct,;
>> PadR(MemberData.cInvoiceNo, 14) AS cInvoiceNo, MemberData.dInvoice,;
>> PadR(MemberData.cPackSize, 10) AS cPackSize, PadR(MemberData.cPackConf, 10) AS cPackConf,;
>> MemberData.nUnits, MemberData.yUnitPrice,;
>> MemberData.nUnits * MemberData.yUnitPrice AS yTotalCost;
>>FROM Supplier;
>>INNER JOIN ProductList;
>>INNER JOIN MemberData;
>>INNER JOIN MemberList;
>>ON MemberList.iKey = MemberData.iKeyMember;
>>ON ProductList.iKey = MemberData.iKeyProduct;
>>ON Supplier.iKey = ProductList.iKeySupplier;
>>INTO CURSOR MemberGrid;
>>ORDER BY MemberList.cName
>>
>>Resulting cursor: 115 837 records
>>
>>SYS(3054, 1) return full optimization on all the tables
>
>
>VFP only joins two tables at a time, and you never know which two it will pick. Let's look at an example:
>
>Supplier and Product List are joined to create Temp1
>Temp1 is not indexed, so the next join is only partially optimized
>Temp1 and MemberData are joined to create Temp2
>Temp2 is not indexed so the next join is only partially optimized
>Temp2 and MemberList are joined to create MemberGrid
>
>Look at the SYS(3054) function. It shows you the optimization levels of the SQL SELECT.

Are you sure VFP will not index temp result in order to get better results? I think VFP does this.

Vlad