query too slow

  • Thread starter Thread starter angie
  • Start date Start date
A

angie

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!
 
(([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
Click to expand...
Click to expand...

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];
Click to expand...
Click to expand...

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 said:
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!
Click to expand...
 
Back
Top