General information
Category:
Coding, syntax & commands
Title:
Optimizing my query
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
Next
Reply
View the map of this thread
View the map of this thread starting from this message only
View all messages of this thread
View all messages of this thread starting from this message only