By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,928 Members | 1,173 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,928 IT Pros & Developers. It's quick & easy.

A problem with UNION

P: n/a
Hi, I'm running a query unifying two relatively simple selections.
The problem is that each selection, on its own, completes
instantaneously, but, unified, the processing takes upwards of 30
seconds. Maybe I just don't understand correctly how a union is
processed, but if anyone can explain this (and, preferably, offer a
solution or alternative), I would appreciate it.

The query in question:
SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE PSA.detectionLimit<>'<' AND PSA.totalPSA IN (SELECT PSA.totalPSA
FROM PSA WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1)

UNION SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1 AND
PSA.totalPSA<>.05;

studyCentreRecords contains about 1500 records
PSA contains about 9000 records

Again, each selection on its own is fine.

Thank you,

Adam Louis
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Adam Louis wrote:
Hi, I'm running a query unifying two relatively simple selections.
The problem is that each selection, on its own, completes
instantaneously, but, unified, the processing takes upwards of 30
seconds. Maybe I just don't understand correctly how a union is
processed, but if anyone can explain this (and, preferably, offer a
solution or alternative), I would appreciate it.

The query in question:
SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE PSA.detectionLimit<>'<' AND PSA.totalPSA IN (SELECT PSA.totalPSA
FROM PSA WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1)

UNION SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1 AND
PSA.totalPSA<>.05;

studyCentreRecords contains about 1500 records
PSA contains about 9000 records

Again, each selection on its own is fine.

Thank you,

Adam Louis

By default, a union will try to eliminate duplicate entries from the
list, if you know there will be no duplicates then use UNION ALL.
--
But why is the Rum gone?
Nov 12 '05 #2

P: n/a
> > The query in question:


SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE PSA.detectionLimit<>'<' AND PSA.totalPSA IN (SELECT PSA.totalPSA
FROM PSA WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1)

UNION SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1 AND
PSA.totalPSA<>.05;


Either I'm not reading carefully enough, or you're making this way
more complicated than it should be. Why not just OR together your
criteria?

SELECT studyCentreRecords.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLimit
FROM PSA INNER JOIN studyCentreRecords ON
PSA.patient=studyCentreRecords.patient
WHERE (PSA.detectionLimit<>'<' AND PSA.totalPSA IN (SELECT
PSA.totalPSA
FROM PSA WHERE PSA.detectionLimit='<' AND PSA.totalPSA<>.1))
OR (PSA.detectionLimit='<' AND PSA.totalPSA<>.1 AND
PSA.totalPSA<>.05);
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.