>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
I believe, that's it's fully optimizable, but assuming that
1. return of significant amount of records,
2. significant number of joined table,
3. order clause on result,
4. main one- functions in result field set (they will fire for each record),
12 min might be trustworthy.
I believe you should reconsider your interface approach. As i guess, you use 'membergrid' cursor as Grid.Recordsource. Probably, 115K is not very representative for users, and it would be more appropriate to coleect smaller recordset, e.g. for particular 'ikeyproduct'. Also, it's not necessarily to join all related tables. Instead of this, you could combine two tables, and add some grid.column.buttons to open dialogs (e.g. 'memberdata' dialog).
Edward Pikman
Independent Consultant