Slow query response

[Evan Miller]

I have a query which is taking a while - and maybe that's ok, but I wanted
to make sure.

I have a table L which contains roughly 73000 records. I have another table
M which contains roughly 11000 records. Both tables have an ID column (F1
in M and code in L). I want to find a list of M records that dont have a
match in L.

The query I'm running is:
SELECT M.F1
FROM M
WHERE M.F1 not in (select code from L);


L.code has a unique index. M.F1 has a non-unique index.


Thoughts?

Thanks,
Evan

 

[Gordon Smith \(eMVP\)]

I have a query which is taking a while - and maybe that's ok, but I
wanted to make sure.

I have a table L which contains roughly 73000 records. I have
another table M which contains roughly 11000 records. Both tables
have an ID column (F1 in M and code in L). I want to find a list of
M records that dont have a match in L.

The query I'm running is:
SELECT M.F1
FROM M
WHERE M.F1 not in (select code from L);


L.code has a unique index. M.F1 has a non-unique index.


Thoughts?

Thanks,
Evan

Sure - use an outer join and filter for L.code is null.

 

[geebee]

Hi,

Here's a start...

Select Tools ---> Analyze --> Performance

Select any queries or tables you are concerned whose performance you are
concerned with. Access will then recommend steps to improve performance.

Thanks

 

[John Spencer]

Use the query wizard to do a find unmatched query.

SELECT M.F1
FROM M LEFT JOIN L
ON M.F1 = L.Code
WHERE L.Code is Null

That should be a lot faster then using "Not IN" which is known to be slow.

 

[Jerry Whittle]

If John Spenser's query doesn't help (but I bet that it does) try the
following:

SELECT M.F1
FROM M
WHERE Not Exists
(SELECT "X"
FROM L
WHERE M.F1 = L.code);

Sometimes a Not Exists / Exists works much better than a Not In / In
statement. I usually try both to see which one works best.