A problem with UNION

[Adam Louis]

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 studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE PSA.detectionLi mit<>'<' AND PSA.totalPSA IN (SELECT PSA.totalPSA
FROM PSA WHERE PSA.detectionLi mit='<' AND PSA.totalPSA<>. 1)

UNION SELECT studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE PSA.detectionLi mit='<' AND PSA.totalPSA<>. 1 AND
PSA.totalPSA<>. 05;

studyCentreReco rds contains about 1500 records
PSA contains about 9000 records

Again, each selection on its own is fine.

Thank you,

Adam Louis

[Trevor Best]

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 studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE PSA.detectionLi mit<>'<' AND PSA.totalPSA IN (SELECT PSA.totalPSA
FROM PSA WHERE PSA.detectionLi mit='<' AND PSA.totalPSA<>. 1)

UNION SELECT studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE PSA.detectionLi mit='<' AND PSA.totalPSA<>. 1 AND
PSA.totalPSA<>. 05;

studyCentreReco rds 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?

[Pieter Linden]

> > The query in question:

SELECT studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE PSA.detectionLi mit<>'<' AND PSA.totalPSA IN (SELECT PSA.totalPSA
FROM PSA WHERE PSA.detectionLi mit='<' AND PSA.totalPSA<>. 1)

UNION SELECT studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE PSA.detectionLi mit='<' 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 studyCentreReco rds.mrn, PSA.patient, PSA.id, PSA.date,
PSA.totalPSA, PSA.detectionLi mit
FROM PSA INNER JOIN studyCentreReco rds ON
PSA.patient=stu dyCentreRecords .patient
WHERE (PSA.detectionL imit<>'<' AND PSA.totalPSA IN (SELECT
PSA.totalPSA
FROM PSA WHERE PSA.detectionLi mit='<' AND PSA.totalPSA<>. 1))
OR (PSA.detectionL imit='<' AND PSA.totalPSA<>. 1 AND
PSA.totalPSA<>. 05);