Slow query response

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Evan Miller - 11 Sep 2006 22:00 GMT

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

Reply to this Message

Gordon Smith (eMVP) - 11 Sep 2006 22:12 GMT

> I have a query which is taking a while - and maybe that's ok, but I
> wanted to make sure.
[quoted text clipped - 15 lines]
> Thanks,
> Evan

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

Signature

Gordon Smith (eMVP)
-- Avnet Applied Computing Solutions

Reply to this Message

geebee - 11 Sep 2006 22:16 GMT

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

> I have a query which is taking a while - and maybe that's ok, but I wanted
> to make sure.
[quoted text clipped - 15 lines]
> Thanks,
> Evan

Reply to this Message

John Spencer - 12 Sep 2006 00:49 GMT

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.

> I have a query which is taking a while - and maybe that's ok, but I wanted
> to make sure.
[quoted text clipped - 15 lines]
> Thanks,
> Evan

Reply to this Message

Jerry Whittle - 12 Sep 2006 14:26 GMT

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.

Signature

Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a query which is taking a while - and maybe that's ok, but I wanted
> to make sure.
[quoted text clipped - 15 lines]
> Thanks,
> Evan

Reply to this Message