Why Does My Union Query Take An Age To Run?

[andybason]

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:

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

Query 2:

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

Union:

(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]

How many records are in the Member table?

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

 

[andybason]

Hi Minty, there are about 30,000 records. How would I join the table? Forgive me I'm learning Access/SQL.

 

[Minty]

It might be easier to see some sample data - your starting data and what you are expecting as a query result.

 

[ebs17]

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.