General information
Category:
Coding, syntax & commands
>>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
>
>
>VFP only joins two tables at a time, and you never know which two it will pick. Let's look at an example:
>
>Supplier and Product List are joined to create Temp1
>Temp1 is not indexed, so the next join is only partially optimized
>Temp1 and MemberData are joined to create Temp2
>Temp2 is not indexed so the next join is only partially optimized
>Temp2 and MemberList are joined to create MemberGrid
>
>Look at the SYS(3054) function. It shows you the optimization levels of the SQL SELECT.
Are you sure VFP will not index temp result in order to get better results? I think VFP does this.
Vlad
Previous
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