query very slow

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

angie

i have a query that takes about five minutes to run. i think that the delay
is not justified: the table contains around 40000 records and the query is
based on two separate queries whose performance is ok.

below is the sql of my query if anyone could help me please!

SELECT [Quotation-Suppliers-qry].[Order Number],
[Quotation-Suppliers-qry].[PART number],
[Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item,
[Quotation-Suppliers-qry].Description, [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]);

i have several other queries based on this one and the delay is a an
obstacle to the database performance.
 
Are the Manufacturer and Part Number fields indexed?

Duane Hookom
MS Access MVP
 
yes, they are both set to indexed: yes (duplicates OK)

Ο χÏήστης "Duane Hookom" έγγÏαψε:
Are the Manufacturer and Part Number fields indexed?

Duane Hookom
MS Access MVP

angie said:
i have a query that takes about five minutes to run. i think that the
delay
is not justified: the table contains around 40000 records and the query is
based on two separate queries whose performance is ok.

below is the sql of my query if anyone could help me please!

SELECT [Quotation-Suppliers-qry].[Order Number],
[Quotation-Suppliers-qry].[PART number],
[Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item,
[Quotation-Suppliers-qry].Description, [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]);

i have several other queries based on this one and the delay is a an
obstacle to the database performance.
Click to expand...
Click to expand...
 
The issue might be in the previous queries. If you provided the SQL views
maybe someone could help.

--
Duane Hookom
Microsoft Access MVP


angie said:
yes, they are both set to indexed: yes (duplicates OK)

Ο χÏήστης "Duane Hookom" έγγÏαψε:
Are the Manufacturer and Part Number fields indexed?

Duane Hookom
MS Access MVP

angie said:
i have a query that takes about five minutes to run. i think that the
delay
is not justified: the table contains around 40000 records and the query is
based on two separate queries whose performance is ok.

below is the sql of my query if anyone could help me please!

SELECT [Quotation-Suppliers-qry].[Order Number],
[Quotation-Suppliers-qry].[PART number],
[Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item,
[Quotation-Suppliers-qry].Description, [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]);

i have several other queries based on this one and the delay is a an
obstacle to the database performance.
Click to expand...
Click to expand...
Click to expand...
 
below the sql statement of the two queries:

qry1:

SELECT [Quotation-Suppliers-qry].Manufacturer,
[Quotation-Suppliers-qry].Item, [Quotation-Suppliers-qry].[PART number],
Min([Quotation-Suppliers-qry].[Net Price]) AS [MinOfNet Price]
FROM [Quotation-Suppliers-qry]
GROUP BY [Quotation-Suppliers-qry].Manufacturer,
[Quotation-Suppliers-qry].Item, [Quotation-Suppliers-qry].[PART number];

qry2:

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] Like "yes",([Expr1]*1.19),[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,
[Quotation-tbl].Country, [Quotation-tbl].Factory, [Quotation-tbl].Valid,
[Supplier] & ([Suppliers-tbl.ID]) AS Expr3
FROM [Quotation-tbl] INNER JOIN [Suppliers-tbl] ON [Quotation-tbl].ID =
[Suppliers-tbl].ID
WHERE ((([Quotation-tbl].Valid)="YES"));

any help would be greatly appreciated.

Duane Hookom said:
The issue might be in the previous queries. If you provided the SQL views
maybe someone could help.

--
Duane Hookom
Microsoft Access MVP


angie said:
yes, they are both set to indexed: yes (duplicates OK)

Ο χÏήστης "Duane Hookom" έγγÏαψε:
Are the Manufacturer and Part Number fields indexed?

Duane Hookom
MS Access MVP

i have a query that takes about five minutes to run. i think that the
delay
is not justified: the table contains around 40000 records and the query is
based on two separate queries whose performance is ok.

below is the sql of my query if anyone could help me please!

SELECT [Quotation-Suppliers-qry].[Order Number],
[Quotation-Suppliers-qry].[PART number],
[Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item,
[Quotation-Suppliers-qry].Description, [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]);

i have several other queries based on this one and the delay is a an
obstacle to the database performance.
Click to expand...
Click to expand...
Click to expand...
 
What are the names of qry1 and qry2 and how do they all fit in? Maybe a
little description of which query is run first, second, etc.

However if you run a query based on a query based on yet another query, it
will be slow. Indexing and such doesn't carry through past the original
query. For example the three Group By fields in qry1 would normally be a good
candidate if they were in a table; however, they come from another query.

If you could redesign these queries into one directly against the tables,
that my help.

I notice that you have criteria of "Yes". Are these fields Text fields or
Yes/No fields? Probably text for them even to work. However in the future
consider using Yes/No fields in such a situation. It could provide a small
amount of performance increase, but probably not enough to warrent a redesign
now.
IIf([Iva] Like "yes",([
Click to expand...

Like is looking for a wildcard. It should be
IIf([Iva] = "yes",([

Make sure that both of these fields are indexed. One of these fields should
be the primary key for its table. If not, that can be a problem.
[Quotation-tbl].ID = [Suppliers-tbl].ID

Here's another field that needs indexing:
((([Quotation-tbl].Valid)="YES"));


--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


angie said:
below the sql statement of the two queries:

qry1:

SELECT [Quotation-Suppliers-qry].Manufacturer,
[Quotation-Suppliers-qry].Item, [Quotation-Suppliers-qry].[PART number],
Min([Quotation-Suppliers-qry].[Net Price]) AS [MinOfNet Price]
FROM [Quotation-Suppliers-qry]
GROUP BY [Quotation-Suppliers-qry].Manufacturer,
[Quotation-Suppliers-qry].Item, [Quotation-Suppliers-qry].[PART number];

qry2:

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] Like "yes",([Expr1]*1.19),[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,
[Quotation-tbl].Country, [Quotation-tbl].Factory, [Quotation-tbl].Valid,
[Supplier] & ([Suppliers-tbl.ID]) AS Expr3
FROM [Quotation-tbl] INNER JOIN [Suppliers-tbl] ON [Quotation-tbl].ID =
[Suppliers-tbl].ID
WHERE ((([Quotation-tbl].Valid)="YES"));

any help would be greatly appreciated.

Duane Hookom said:
The issue might be in the previous queries. If you provided the SQL views
maybe someone could help.

--
Duane Hookom
Microsoft Access MVP


angie said:
yes, they are both set to indexed: yes (duplicates OK)

Ο χÏήστης "Duane Hookom" έγγÏαψε:

Are the Manufacturer and Part Number fields indexed?

Duane Hookom
MS Access MVP

i have a query that takes about five minutes to run. i think that the
delay
is not justified: the table contains around 40000 records and the query is
based on two separate queries whose performance is ok.

below is the sql of my query if anyone could help me please!

SELECT [Quotation-Suppliers-qry].[Order Number],
[Quotation-Suppliers-qry].[PART number],
[Quotation-Suppliers-qry].Manufacturer, [Quotation-Suppliers-qry].Item,
[Quotation-Suppliers-qry].Description, [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]);

i have several other queries based on this one and the delay is a an
obstacle to the database performance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top