Query taking too long, suggestions?

[theperson]

Hello,

I just created a query that is built from 2 tables and 6 queries. 5 of those queries I built specifically for this query. Now this query works, but it takes >1 min to load and each new record I add to it takes it longer and longer so I'm curious if there's a better way to do this.

The reason I built 5 supporting queries is because there is data that is needed from the same table but for different part #'s. This is an inventory table, so say part #1234 takes part#5678 and part #8910 to be made. Well I need the inventories for both 5678 and 8910 but those inventories are taken from the same table. So that's why I have multiple queries for different part # inventories that are being stored in the same table.

Is there an easier way to do this? If this didn't make sense I may be able to explain/make a mock database up showing what I'm doing.

EDIT: An interesting note is that if I take away any one of the 5 supporting queries, the main query has no trouble at all. But once I add that query back in, the main query hangs forever.



Thanks!

[rpeare]

Are you using dlookup or dsum commands in your queries?

[theperson]

I don't think I'm running any calculations in any queries.

[rpeare]

how are you putting your query together is it a set of union queries? how large is the largest table (rows and columns) you're using in the query?

Can you give an example (the SQL) of one of the 5 supporting queries?

[theperson]

There are no union queries used.

Example of sql of supporting query:

SELECT [Master List].PurchasedPart3, MaterialInvBase.[Inventory-Current]FROM [Master List] INNER JOIN MaterialInvBase ON [Master List].PurchasedPart3 = MaterialInvBase.[Part#];

The largest table is 500 rows and about 50 columns.

[rpeare]

Something that small shouldn't be having trouble at all, particularly if you're not performing any calculations at all.

Can you provide a sample of the database (just the part that you're having a question on with some sample data to make it work)

[JoeM]

Also, setting up proper indexes at the table level helps query performance.
See: http://office.microsoft.com/en-us/ac...010210347.aspx

[theperson]

I've attached a condensed sample database of what I'm dealing with.

If you open the "AssemblyReportMainFIX", it doesn't take too long.

If you open the "AssemblyReportMain", it takes forever.

Everytime I delete one of those supporting queries out of the main query, it becomes faster. It almost looks like an exponential kind of thing.

I indexed every field I could index and nothing improved.

What do you think is wrong?

[rpeare]

Try this query:

SELECT DISTINCT [Master List].[Part#], [Master List].Assembly, [Inventory Base].[Inventory-Current] AS [Part#Inventory], [Master List].MoldPart1, [Inventory Base_1].[Inventory-Current] AS MoldPart1Inventory, [Inventory Base_4].[part#] AS PurchasedPart2, [Inventory Base_4].[Inventory-Current] AS PurchasedPart2Inventory, [Inventory Base_3].[part#] AS PurchasedPart1, [Inventory Base_3].[Inventory-Current] AS PurchasedPart1Inventory, [Inventory Base_5].[part#] AS PurchasedPart3, [Inventory Base_5].[Inventory-Current] AS PurchasedPart3Inventory, [Inventory Base_2].[part#] AS MoldPart2, [Inventory Base_2].[Inventory-Current] AS MoldPart2Inventory, [Master List].TargetInventory
FROM ((((([Inventory Base] INNER JOIN [Master List] ON [Inventory Base].[Part#] = [Master List].[Part#]) LEFT JOIN [Inventory Base] AS [Inventory Base_1] ON [Master List].MoldPart1 = [Inventory Base_1].[Part#]) LEFT JOIN [Inventory Base] AS [Inventory Base_2] ON [Master List].MoldPart2 = [Inventory Base_2].[Part#]) LEFT JOIN [Inventory Base] AS [Inventory Base_3] ON [Master List].PurchasedPart1 = [Inventory Base_3].[Part#]) LEFT JOIN [Inventory Base] AS [Inventory Base_4] ON [Master List].PurchasedPart2 = [Inventory Base_4].[Part#]) LEFT JOIN [Inventory Base] AS [Inventory Base_5] ON [Master List].PurchasedPart3 = [Inventory Base_5].[Part#]
WHERE ((([Master List].Assembly)=Yes));

[Dal Jeanis]

Aliasing the tables will help you write it as a single readable query, which Jet will be much more efficient at performing. Try coding the multijoin something like this:

SELECT 
ML.PurchasedPart1, MatP1.[Inventory-Current] As InventoryP1,
ML.PurchasedPart2, MatP2.[Inventory-Current] As InventoryP2,
ML.PurchasedPart3, MatP3.[Inventory-Current] As InventoryP3
FROM [Master List] AS ML, MaterialInvBase AS MatP1, MaterialInvBase AS MatP2, MaterialInvBase AS MatP3 
WHERE ML.PurchasedPart1 = MatP1.[Part#] 
AND ML.PurchasedPart2 = MatP2.[Part#] 
AND ML.PurchasedPart3 = MatP3.[Part#];

Add your mold parts the same way. You could also specify the same thing in the INNER JOIN ... ON format, and Access may turn it into that format for you anyway. FYI, you can Alias a table in the query builder by right-clicking the table and changing the name.

By the way, the design of having exactly two mold and three purchased parts per record screams that you have a basic normalization error. If the purchased parts were in a separate table - which they'd have to be to reach first normal form - you could (A) have any number of them from zero to infinity, and (B) do this query step with a single simple join. Just a thought.

I also believe that your query won't tell you whether you can build everything you currently want to, just whether you can build each thing you currently want to. In order to know if you can build everything, you'd need to be summing all the individual requirements and comparing the total against your current inventory.

[Dal Jeanis]

Yes... If the PurchasedPart can be NULL, then you'd need to change those to LEFT JOINS, as rpeare coded them. Another great reason to normalize your design...

[theperson]

Thanks rpeare and Dal! I used rpeare's code and it worked. It even allows me to not put the placeholder "-" in for parts that don't purchased parts/molded parts.