Information générale
Catégorie:
Codage, syntaxe et commandes
Titre:
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement