How can I improve my query?

[Vaughan]

I am trying to query an existing "Bill of Materials" SELECT query that
returns Warehouse and Product Code data exploded to a depth of four levels
(i.e. Level one assemblies are made up of level two components and
sub-assemblies, which in turn comprise level 3 components etc) to create a
list of all the items at each level in a final assembly contained in two
warehouses. This is how I did it using a Union Query.

The thing is that the [BOM Explosion] sub-query is quite complex and takes a
while to run. I think my query is running it five times. At any rate, it
takes an age to complete. A number of other reports rely on the [BOM
Explosion] query, so I can't touch it. Is there something I can do to improve
the efficiency of my query without re-writing the [BOM Explosion] sub query?

SELECT
assembly_warehouse AS WH,
product_code AS Prod
FROM [BOM Explosion]
WHERE assembly_warehouse In('OM','MS')

UNION SELECT
Level1_warehouse AS WH,
Level1_component AS Prod
FROM [BOM Explosion]
WHERE Level1_warehouse In('OM','MS')

UNION SELECT
Level2_warehouse AS WH,
Level2_component AS Prod
FROM [BOM Explosion]
WHERE Level2_warehouse In('OM','MS')

UNION SELECT
Level3_warehouse AS WH,
Level3_component AS Prod
FROM [BOM Explosion]
WHERE Level3_warehouse In('OM','MS')

UNION SELECT
Level4_warehouse AS WH,
Level4_component AS Prod
FROM [BOM Explosion]
WHERE Level4_warehouse In('OM','MS')
;

Many thanks for your help folks.

Vaughan

 

[Marshall Barton]

I am trying to query an existing "Bill of Materials" SELECT query that
returns Warehouse and Product Code data exploded to a depth of four levels
(i.e. Level one assemblies are made up of level two components and
sub-assemblies, which in turn comprise level 3 components etc) to create a
list of all the items at each level in a final assembly contained in two
warehouses. This is how I did it using a Union Query.

The thing is that the [BOM Explosion] sub-query is quite complex and takes a
while to run. I think my query is running it five times. At any rate, it
takes an age to complete. A number of other reports rely on the [BOM
Explosion] query, so I can't touch it. Is there something I can do to improve
the efficiency of my query without re-writing the [BOM Explosion] sub query?

SELECT
assembly_warehouse AS WH,
product_code AS Prod
FROM [BOM Explosion]
WHERE assembly_warehouse In('OM','MS')

UNION SELECT
Level1_warehouse AS WH,
Level1_component AS Prod
FROM [BOM Explosion]
WHERE Level1_warehouse In('OM','MS')

UNION SELECT
Level2_warehouse AS WH,
Level2_component AS Prod
FROM [BOM Explosion]
WHERE Level2_warehouse In('OM','MS')

UNION SELECT
Level3_warehouse AS WH,
Level3_component AS Prod
FROM [BOM Explosion]
WHERE Level3_warehouse In('OM','MS')

UNION SELECT
Level4_warehouse AS WH,
Level4_component AS Prod
FROM [BOM Explosion]
WHERE Level4_warehouse In('OM','MS')

Most likely the slowness is coming from the explosion query.

Assuming your UNION query gives you the desired dataset, the
only thing that I see to improve it is to use UNION ALL.

 

[Gary Walter]

Sometimes you just have to append to a
table to reduce exection time.

Turn your [BOM Explosion] Union subquery
into a make table query.

Run it.

Then change query to an append query.

Whenever you need it, in VBA empty the
table and run the append query.

Then use table in other queries.