Why is this query so slow?

[Allen_N]

I am working on a database of redundant kits, trying to identify all those
with items in common with a reference kit. The quantities of each item need
to be identical between the candidate kit and the reference kit (a table
called [Find Items In Kits]).

After futile hours spent trying to select all records that meet my criteria,
things looked brighter when I tried the converse. This query correctly
returns all kits that have the right items (previously selected by the query
[Kits Found Items], which has 67 rows), but the wrong quantities thereof:

SELECT [Kits Found Items].KitItem FROM [Kits Found Items]
INNER JOIN [Find Items In Kits]
ON [Kits Found Items].Item = [Find Items In Kits].[Part Number]
WHERE [Find Items In Kits].[Quantity] <> [Kits Found Items].Per;

When I implement this as a negated inner query, as so:

SELECT [Kits Found Items].KitItem FROM [Kits Found Items]
WHERE [Kits Found Items].KitItem
NOT IN
(SELECT [Kits Found Items].KitItem FROM [Kits Found Items]
INNER JOIN [Find Items In Kits]
ON [Kits Found Items].Item = [Find Items In Kits].[Part Number]
WHERE [Find Items In Kits].[Quantity] <> [Kits Found Items].Per);

.... I seem to get the correct result, but this 'compound' query takes ~ 30
sec, whereas the inner one alone took ~ 2 sec.

Can anyone see if I'm doing something dumb?

(N.B. [KitItem] is the name of a kit, [Item] is the name an item, and [Per]
or [Quantity] are the number of pieces of that item within a parent kit.)

Thanks!

 

[John Spencer]

Not in is always slow.

Perhaps you can satisify your need using a frustrated outer join query. Since
you have spaces in your table names you will have to use a saved query.

QueryA (or whatever name you choose to give the query.
SELECT [Kits Found Items].KitItem
FROM [Kits Found Items]
INNER JOIN [Find Items In Kits]
ON [Kits Found Items].Item = [Find Items In Kits].[Part Number]
WHERE [Find Items In Kits].[Quantity] <> [Kits Found Items].Per);

Second query using the original table and the stored query would look like

SELECT [K].KitItem
FROM [Kits Found Items] as K LEFT JOIN QueryOne as Q