Why Does My Union Query Take An Age To Run? (1 Viewer)

andybason

New member
Local time
Today, 20:06
Joined
Nov 19, 2020
Messages
2
Hi guys,

I am trying to put together a union query to combine two other queries. The existing queries run in less than a second but combining them takes 5-10 minutes.

I've tried "UNION ALL" as well but get the same thing.

Can anyone see the issue?

Thanks

Query 1:

Code:
SELECT Member.*
FROM Member
WHERE Member.IDNo = "AAAAA" AND uniqueRef IN (SELECT uniqueRef FROM Member WHERE IDNo = "BBBBB");

Query 2:

Code:
SELECT Member.*
FROM Member
WHERE Member.IDNo = "BBBBB" AND uniqueRef IN (SELECT uniqueRef FROM Member WHERE IDNo = "AAAAA");

Union:

Code:
(SELECT Member.*
FROM Member
WHERE Member.IDNo = "AAAAA" AND uniqueRef IN (SELECT uniqueRef FROM Member WHERE IDNo = "BBBBB"))

UNION

(SELECT Member.*
FROM Member
WHERE Member.IDNo = "BBBBB" AND uniqueRef IN (SELECT uniqueRef FROM Member WHERE IDNo = "AAAAA"));
 

Minty

AWF VIP
Local time
Today, 20:06
Joined
Jul 26, 2013
Messages
10,355
How many records are in the Member table?

Couldn't you just join the table to itself instead of the sub query?
 

andybason

New member
Local time
Today, 20:06
Joined
Nov 19, 2020
Messages
2
Hi Minty, there are about 30,000 records. How would I join the table? Forgive me I'm learning Access/SQL.
 

Minty

AWF VIP
Local time
Today, 20:06
Joined
Jul 26, 2013
Messages
10,355
It might be easier to see some sample data - your starting data and what you are expecting as a query result.
 

ebs17

Well-known member
Local time
Today, 21:06
Joined
Feb 7, 2020
Messages
1,881
Code:
SELECT *
FROM Member
WHERE 
   IDNo = "AAAAA" AND uniqueRef IN (SELECT uniqueRef FROM Member WHERE IDNo = "BBBBB")
      OR
   IDNo = "BBBBB" AND uniqueRef IN (SELECT uniqueRef FROM Member WHERE IDNo = "AAAAA")

IDNo and uniqueRef should be indexed.
 

Users who are viewing this thread

Top Bottom