Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Optimizing my query
Message
From
26/11/1997 09:19:12
 
 
To
All
General information
Forum:
Visual FoxPro
Category:
Coding, syntax & commands
Title:
Optimizing my query
Miscellaneous
Thread ID:
00062387
Message ID:
00062387
Views:
88
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
Map
View

Click here to load this message in the networking platform