Query with no criteria takes forever

  • Thread starter Thread starter Phil Smith
  • Start date Start date
P

Phil Smith

I have a query, several tables, 1 to many, yada yada.

I have several fields I use criteria in, for date ranges and stuff, and
one field called brand_id, a long integer. Values for this field range
from 1 to 27 in this field.

If I run this with no criteria for this field, it takes forever. It
should take about a second.
If I put say, "between 1 and 27," it flies. 1 second tops.
<=27 it flies.
=0 it flies
Click to expand...

But NO criteria, or, interestingly enough, "is null" or is not null", it
takes something in to 20 minute range.

There are no null values in this field.

Any ideas? Obviously I can get around it, and have done so, but this
ispretty bizzare behavior, even for Access...

Phil
 
Go into query designer. Click view -> SQL View. Copy what is in
there and paste it here, then give some information about the fields
used and we'll be able to help you a little better.

Cheers,
Jason Lepack
 
Phil

How many rows are returned when you use the criteria?

How many rows are returned when you use NO criteria?

Where is the data located (on a file server on a LAN; on a SQL-Server; over
a WAN; ...)?

How many folks are using the application at the same time?

Are the underlying "join", "sort" and "criteria" fields indexed in their
respective tables?

More info, please...

(and as Jason points out, a look at the SQL statement might help)

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
OK. This works fine:

SELECT brand.name,
Sum(IIf(invoice_h!sales_rep1type="P",invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep1pct/100,0)+IIf(invoice_h!sales_rep2type="P",invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep2pct/100,0)+IIf(invoice_h!sales_rep3type="P",invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep3pct/100,0))
AS Ext_Commision
FROM brand RIGHT JOIN ((customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON (invoice_d.company_id = invoice_h.company_id) AND
(invoice_d.invoice_id = invoice_h.invoice_id)) ON customer.customer_id =
invoice_h.customer_id) LEFT JOIN item ON invoice_d.item_id =
item.item_id) ON brand.brand_id = item.brand_id
WHERE (((invoice_d.ship_qty)<>0) AND ((invoice_d.price)<>0) AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9))
GROUP BY brand.name, item.brand_id
HAVING (((item.brand_id)>=0))
ORDER BY brand.name;


Remove the Having, or change it to IS NULL or IS NOT NULL, and the query
takes forever.
 
OK.
Since it is a summary query, there is no way to know for sure if I am
getting the same number of rows, but I do get identical results either way.
The data is coming from MYSQL running over a LAN.
This problem happens even when only a single user is on the database.
Date and status are indexed, ship_qty and price are not. The field in
question, Brand_id is not indexed.
One thing of interest, maybe?
When it works fast, I don't even see a progress bar. It is just there.
When it works slow, I see a progress bar which fills up in about two
seconds. It then hangs for the 20 minutes or so.
 
item.brand_id is most likely indexed as by default Access indexes anything
with "id" in the field name. Therefore it can find anything >0 or <27 very
efficiently. However indexes don't track nulls. Therefore the index isn't
being used if you are asking about Nulls.

Also you have a strange combination of left, right, and inner joins. Access
may be having a problem with how to optimize the query to run best. Most
likely the Having criteria wakes up Access and says "Use the Index!". If you
could change the order of the joins, that might help.

To remove all doubt, you could install ShowPlan and see how the query is
being run including use of indexes. Do a google on Showplan Access to find
out more.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Phil Smith said:
OK. This works fine:

SELECT brand.name,
Sum(IIf(invoice_h!sales_rep1type="P",invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep1pct/100,0)+IIf(invoice_h!sales_rep2type="P",invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep2pct/100,0)+IIf(invoice_h!sales_rep3type="P",invoice_d!ship_qty*invoice_d!price*invoice_h!sales_rep3pct/100,0))
AS Ext_Commision
FROM brand RIGHT JOIN ((customer INNER JOIN (invoice_d INNER JOIN
invoice_h ON (invoice_d.company_id = invoice_h.company_id) AND
(invoice_d.invoice_id = invoice_h.invoice_id)) ON customer.customer_id =
invoice_h.customer_id) LEFT JOIN item ON invoice_d.item_id =
item.item_id) ON brand.brand_id = item.brand_id
WHERE (((invoice_d.ship_qty)<>0) AND ((invoice_d.price)<>0) AND
((invoice_h.invoice_date)>=[Forms]![Sales_Reports_Form]![STARTDATE] And
(invoice_h.invoice_date)<=[Forms]![Sales_Reports_Form]![ENDDATE]) AND
((invoice_h.status)=8 Or (invoice_h.status)=9))
GROUP BY brand.name, item.brand_id
HAVING (((item.brand_id)>=0))
ORDER BY brand.name;


Remove the Having, or change it to IS NULL or IS NOT NULL, and the query
takes forever.



Jason said:
Go into query designer. Click view -> SQL View. Copy what is in
there and paste it here, then give some information about the fields
used and we'll be able to help you a little better.

Cheers,
Jason Lepack
Click to expand...
Click to expand...
 
Thanx for that.
Showplan verifies that there is no index for brand_ID.

I am not sure what you mean by strange combo of joins.
I have an invoice header which joins to the customer, and the invoice
detail, which in turn joins to the Item, which joins to the brand of
that item. Seems relatively straightforward, and I am not sure how I
would join it differently.
 
Back
Top