If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query too slow
i have a table with 500000 records. it consists of pricelists from various
suppliers so i have duplicate products with diffrent prices and supplier information. i want to create a query that retrieves only the minimum price offer for each product. i have created a query with the following sql statement to retrieve minimum prices: SELECT [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].[PART number], Min([Suppliers-tbl].[Net Price]) AS [MinOfNet Price] FROM [Quotation-tbl] INNER JOIN [Suppliers-tbl] ON [Quotation-tbl].ID = [Suppliers-tbl].ID WHERE ((([Quotation-tbl].Valid)="yes")) GROUP BY [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].[PART number]; i have another query that calculates final prices for the products with the sql statement below: SELECT [Suppliers-tbl].[Order Number], [Suppliers-tbl].[PART number], [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].Description, [Suppliers-tbl].[Net Price], IIf([Commission]0,[Net Price]*(([Commission]/100)+1),[Net Price]) AS Expr1, IIf([Iva]="yes",([Expr1]*1.21),[Expr1]) AS Expr2, [Suppliers-tbl].MinOrderQty, [Suppliers-tbl].ID, [Quotation-tbl].Supplier, [Quotation-tbl].Commission, [Quotation-tbl].Iva, [Quotation-tbl].MinOrderValue, [Quotation-tbl].DeliveryDays, [Quotation-tbl].ShippingTerms, [Quotation-tbl].PaymentTerms, [Quotation-tbl].ValidThrough, [Quotation-tbl].Status, [Company-tbl].Country, [Quotation-tbl].Factory, [Quotation-tbl].Valid, [Supplier] & ([Suppliers-tbl.ID]) AS Expr3 FROM [Company-tbl] INNER JOIN ([Quotation-tbl] INNER JOIN [Suppliers-tbl] ON [Quotation-tbl].ID = [Suppliers-tbl].ID) ON [Company-tbl].NameID = [Quotation-tbl].Supplier WHERE ((([Quotation-tbl].Valid)="YES")); and below is the sql statement of the query that takes more than five minutes to run: SELECT [Quotation-Suppliers-qry].[Order Number], [Quotation-Suppliers-qry].[PART number], [Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item, [Net Price]-[MinOfNet Price] AS Expr4, [Pricelist-qry-0].[MinOfNet Price], [Quotation-Suppliers-qry].[Net Price], [Quotation-Suppliers-qry].Expr2, [Quotation-Suppliers-qry].MinOrderQty, [Quotation-Suppliers-qry].ID, [Quotation-Suppliers-qry].Supplier, [Quotation-Suppliers-qry].Commission, [Quotation-Suppliers-qry].Iva, [Quotation-Suppliers-qry].MinOrderValue, [Quotation-Suppliers-qry].DeliveryDays, [Quotation-Suppliers-qry].ShippingTerms, [Quotation-Suppliers-qry].PaymentTerms, [Quotation-Suppliers-qry].ValidThrough, [Quotation-Suppliers-qry].Status, [Quotation-Suppliers-qry].Country, [Quotation-Suppliers-qry].Factory, [Quotation-Suppliers-qry].Valid, [Quotation-Suppliers-qry].Expr3 FROM [Pricelist-qry-0] INNER JOIN [Quotation-Suppliers-qry] ON ([Pricelist-qry-0].Manufacturer = [Quotation-Suppliers-qry].Manufacturer) AND ([Pricelist-qry-0].[PART number] = [Quotation-Suppliers-qry].[PART number]); any advise on how to solve this problem? perhaps i should create a module instead of a query and if yes how? pls help me solve this out! |
#2
|
|||
|
|||
query too slow
(([Quotation-tbl].Valid)="yes"))
Run the following SQL statement: Select Valid, Count(Valid) From [Quotation-tbl] Group By Valid ; Select Count(Valid) From [Quotation-tbl] ; If "yes" is significantly less than half the records, or there is many more options than "yes" or "no" (such as nulls), you may want to ensure that Valid is indexed. [Quotation-tbl].ID = [Suppliers-tbl].ID Both of these fields should be indexed. Ideally at least one of them is a primary key field and already indexed. [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].[PART number]; It might help if these fields were indexed. You might want to test this using a stopwatch. Still the problem is that you have a query running off of other queries. If you could rewrite the third query to directly hit the tables, it might speed things up. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "angie" wrote: i have a table with 500000 records. it consists of pricelists from various suppliers so i have duplicate products with diffrent prices and supplier information. i want to create a query that retrieves only the minimum price offer for each product. i have created a query with the following sql statement to retrieve minimum prices: SELECT [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].[PART number], Min([Suppliers-tbl].[Net Price]) AS [MinOfNet Price] FROM [Quotation-tbl] INNER JOIN [Suppliers-tbl] ON [Quotation-tbl].ID = [Suppliers-tbl].ID WHERE ((([Quotation-tbl].Valid)="yes")) GROUP BY [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].[PART number]; i have another query that calculates final prices for the products with the sql statement below: SELECT [Suppliers-tbl].[Order Number], [Suppliers-tbl].[PART number], [Suppliers-tbl].Manufacturer, [Suppliers-tbl].Item, [Suppliers-tbl].Description, [Suppliers-tbl].[Net Price], IIf([Commission]0,[Net Price]*(([Commission]/100)+1),[Net Price]) AS Expr1, IIf([Iva]="yes",([Expr1]*1.21),[Expr1]) AS Expr2, [Suppliers-tbl].MinOrderQty, [Suppliers-tbl].ID, [Quotation-tbl].Supplier, [Quotation-tbl].Commission, [Quotation-tbl].Iva, [Quotation-tbl].MinOrderValue, [Quotation-tbl].DeliveryDays, [Quotation-tbl].ShippingTerms, [Quotation-tbl].PaymentTerms, [Quotation-tbl].ValidThrough, [Quotation-tbl].Status, [Company-tbl].Country, [Quotation-tbl].Factory, [Quotation-tbl].Valid, [Supplier] & ([Suppliers-tbl.ID]) AS Expr3 FROM [Company-tbl] INNER JOIN ([Quotation-tbl] INNER JOIN [Suppliers-tbl] ON [Quotation-tbl].ID = [Suppliers-tbl].ID) ON [Company-tbl].NameID = [Quotation-tbl].Supplier WHERE ((([Quotation-tbl].Valid)="YES")); and below is the sql statement of the query that takes more than five minutes to run: SELECT [Quotation-Suppliers-qry].[Order Number], [Quotation-Suppliers-qry].[PART number], [Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item, [Net Price]-[MinOfNet Price] AS Expr4, [Pricelist-qry-0].[MinOfNet Price], [Quotation-Suppliers-qry].[Net Price], [Quotation-Suppliers-qry].Expr2, [Quotation-Suppliers-qry].MinOrderQty, [Quotation-Suppliers-qry].ID, [Quotation-Suppliers-qry].Supplier, [Quotation-Suppliers-qry].Commission, [Quotation-Suppliers-qry].Iva, [Quotation-Suppliers-qry].MinOrderValue, [Quotation-Suppliers-qry].DeliveryDays, [Quotation-Suppliers-qry].ShippingTerms, [Quotation-Suppliers-qry].PaymentTerms, [Quotation-Suppliers-qry].ValidThrough, [Quotation-Suppliers-qry].Status, [Quotation-Suppliers-qry].Country, [Quotation-Suppliers-qry].Factory, [Quotation-Suppliers-qry].Valid, [Quotation-Suppliers-qry].Expr3 FROM [Pricelist-qry-0] INNER JOIN [Quotation-Suppliers-qry] ON ([Pricelist-qry-0].Manufacturer = [Quotation-Suppliers-qry].Manufacturer) AND ([Pricelist-qry-0].[PART number] = [Quotation-Suppliers-qry].[PART number]); any advise on how to solve this problem? perhaps i should create a module instead of a query and if yes how? pls help me solve this out! |
Thread Tools | |
Display Modes | |
|
|