>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

Sylvain, the other suggestions are excellent, but in addition I have noticed that a SELECT slows down when you have a parent/child/grandchild relation (in yours you have MemberList, ProductList, Supplier.

My solution is to break the list into 2 Select commands. In one case the time spent went from 6 minutes to 1.2 seconds. In all my test cases my SELECT reported fully optimized as yours does.

HTH
Barbara