Query takes 30 seconds - 1 minute to process. Trying to rework it to take 5 or less

[breezett93]

I am trying to address the slowness of our material billings page. How the page currently works is after clicking the button, the billings form loads and shows all orders that are not complete/open billings. This currently takes around 45 seconds, and it's displays between 500-800 records on average.

The users have deemed it more valuable to save time than to see all the records at once. So, I removed the record source from the form and placed it in the order selection combo box. Now the billings page opens immediately showing nothing which is fine.

I changed how the query works; so that instead of grabbing all material items tied to open orders, it now should only grab items associated with the order selected from the drop down. Each order has 10 billings at most; so this should be almost instant. However, after making these changes, the page still takes the same amount of time to show the selected order.

So, here is detailed information about how the queries originally worked.

QryA: uses the ItemTbl, ItemCategoryTbl, qry1, qry2, qry3 all with option 2 joins pointing back to ItemTbl
qry1: uses BillofMaterialsTbl to sum the billed quantity for each item
qry2: uses the PurchaseOrderTbl to sum the received quantity for each item and remove completed orders
qry3: uses the PurchaseOrderTbl to sum the inventory quantity for each item

QryB: uses the BillofMaterialsTbl and QryA

QryB was the record source for the material billings form.

Here is what I changed.
QryB: now only uses the BillofMaterialsTbl with a Criteria of the order number in the drop down box.

I have tested this query on it's own, and it immediately displays the order with the handful of items billed to it.

QryC: uses QryB and QryA with an Include all records from QryB and only records from QryA where the joiend fields are equal (option 2 join).

My understanding to this is that QryA should only be pulling records that match QryB, instead of pulling all open orders. Something must not be right though because it still takes the same amount of time on the form.

Thanks

 

[dhookom]

I won't begin to try understand your queries but can suggest you make sure you have the significant fields indexed.

 

[gmmastros]

Sounds to me like your original (slow) query is somehow getting executed, even if you don't think it is. Just for fun, and just for temporary, try adding to the where clause "Where 1 = 0". If the original query is still somehow running (without you wanting it to) this where clause should return almost immediately with no rows.

 

[Chriss Miller]

Even with all the details you explain, we know nothing about the database, number of records, keys, and - most important of all - index and actual SQL.

I understand when you have queried all incompleted orders and that's fast, then you already have made the page btter usable, selecting one order and only fetching its data should be fast. Something doesn't work as you assume, though, and it's not at all possible to tell without hands on all of this, data, queries, Access form code.

There is a performance analyzer you should use to find out where what time is spent.

 

[breezett93]

I have verified that both tables being used (ItemTbl and BMatTbl) are using indexes on the fields getting queried a bunch. The key fields would be OrderID and ItemID. Both of them are PK; so they are automatically indexed anyway.

Qry04 is the slow query, QryA in my first post. When I run it by itself, it usually takes 20 seconds or so to process. It shows 3,174 items (which is every item in existence in our system) with the extra calculated fields.

I just want this query to only run on the items provided by Qry06, QryB in my first post.

 

[Chriss Miller]

The key fields would be OrderID and ItemID. Both of them are PK; so they are automatically indexed anyway.

If you want a fast query of all items of an order they have to be indexed on ther FK, orderid, indexes on PK don't help accelerate that.
You want to find all records with the same single FK value, therefore an index on the FK is important.