UNION query taking 5 minutes for approx 2000 records - any tune uptips?

I am dealing with data from two hospitals, each with their version of
the database. I am trying to group records of admissions and
discharges from each hospital by patient (URN), date of admission
(DOA), date of discharge (DISCH) and time of admission (TOA) where at
least one patient record represents a transfer from hosp A (KEMH) to
hosp B (PMH) ie where demogr_urPM.ADMIT_SOURCE Like "KEMH*".

The individual SELECT queries run quickly enough but the UNION query I
am using takes approx 5 min to return about 2000 records. The source
tables, demogr_ur (for hosp A) and demogr_urPM (for hosp B) each have
a compound primary key (URN, ADMN) and are also indexed on DOA.

Do you have any suggestions as to how I can make this faster?

SELECT demogr_urPM.URN, demogr_urPM.ADMN, "PMH" AS Source, Year([DOA])
AS [Year], [DISCH]-[DOA] AS StayDays, demogr_urPM.DOA,
demogr_urPM.TOA, demogr_urPM.ADMIT_SOURCE, [qry_hosp-nmePM_1].HOSP AS
AdmitSource, demogr_urPM.DISCH, demogr_urPM.DISPL, [qry_hosp-
nmePM].HOSP AS DischDest, demogr_urPM.DISSTATUS, status.Status, IIf
([DISPL] Like "KEMH*","KEMH",IIf([DISPL] Is Not Null,IIf([DISPL]
="DIED","Died",IIf([DISPL]="HOME","Home","Elsewhere")),"Unknown")) AS
DischTo, demogr_urPM.DISCHME
FROM ((demogr_urPM LEFT JOIN status ON demogr_urPM.DISSTATUS = status.
[Dis Code]) LEFT JOIN [qry_hosp-nmePM] ON demogr_urPM.DISPL =
[qry_hosp-nmePM].HOSP_ABBREV) LEFT JOIN [qry_hosp-nmePM] AS [qry_hosp-
nmePM_1] ON demogr_urPM.ADMIT_SOURCE = [qry_hosp-nmePM_1].HOSP_ABBREV
WHERE (((demogr_urPM.URN) In (SELECT demogr_urPM.URN
FROM demogr_urPM
WHERE demogr_urPM.ADMIT_SOURCE Like "KEMH*" AND demogr_urPM.ADMN 0
And demogr_urPM.ADMN 10)) AND ((demogr_urPM.ADMN)0 And
(demogr_urPM.ADMN)10))
UNION ALL SELECT demogr_ur.URN, demogr_ur.ADMN, "KEMH" AS Source, Year
([DOA]) AS [Year], [DISCH]-[DOA] AS StayDays, demogr_ur.DOA,
demogr_ur.TOA, demogr_ur.ADMIT_SOURCE, [qry_hosp-nme_1].HOSP AS
AdmitSource, demogr_ur.DISCH, demogr_ur.DISPL, [qry_hosp-nme].HOSP AS
DischDest, demogr_ur.DISSTATUS, status.Status, IIf([DISPL] Like
"KEMH*","KEMH",IIf([DISPL] Is Not Null,IIf([DISPL]="DIED","Died",IIf
([DISPL]="HOME","Home","Elsewhere")),"Unknown")) AS DischTo,
demogr_ur.DISCHME
FROM ((demogr_ur LEFT JOIN status ON demogr_ur.DISSTATUS = status.[Dis
Code]) LEFT JOIN [qry_hosp-nme] ON demogr_ur.DISPL = [qry_hosp-
nme].HOSP_ABBREV) LEFT JOIN [qry_hosp-nme] AS [qry_hosp-nme_1] ON
demogr_ur.ADMIT_SOURCE = [qry_hosp-nme_1].HOSP_ABBREV
WHERE (((demogr_ur.URN) In (SELECT demogr_urPM.URN
FROM demogr_urPM
WHERE demogr_urPM.ADMIT_SOURCE Like "KEMH*" AND demogr_urPM.ADMN 0
And demogr_urPM.ADMN 10)) AND ((demogr_ur.ADMN)0 And (demogr_ur.ADMN)
10))
ORDER BY URN ASC, DOA ASC, DISCH ASC, TOA ASC;

Kind regards,
John McTigue

I notice that they are slow queries (WHERE ... IN ..),
but it will probably be the ORDER BY clause. It can't use
the indexes once the recordset is UNIONed.

If this is going to a report, the order, and the slowness,
will be in the report when it is run as a report: the query
sort order is discarded anyway. People will sometimes
accept slow reports which come out on the printer anyway.

To make it faster, run it into a temp table and then sort
it, or run the IN (SELECT...) queries into a temp table
or switch to SQL Server and see if that has a faster
method of doing pre-selected queries.

(david)

"JohnM" wrote in message
...
I am dealing with data from two hospitals, each with their version of
the database. I am trying to group records of admissions and
discharges from each hospital by patient (URN), date of admission
(DOA), date of discharge (DISCH) and time of admission (TOA) where at
least one patient record represents a transfer from hosp A (KEMH) to
hosp B (PMH) ie where demogr_urPM.ADMIT_SOURCE Like "KEMH*".

The individual SELECT queries run quickly enough but the UNION query I
am using takes approx 5 min to return about 2000 records. The source
tables, demogr_ur (for hosp A) and demogr_urPM (for hosp B) each have
a compound primary key (URN, ADMN) and are also indexed on DOA.

Do you have any suggestions as to how I can make this faster?

SELECT demogr_urPM.URN, demogr_urPM.ADMN, "PMH" AS Source, Year([DOA])
AS [Year], [DISCH]-[DOA] AS StayDays, demogr_urPM.DOA,
demogr_urPM.TOA, demogr_urPM.ADMIT_SOURCE, [qry_hosp-nmePM_1].HOSP AS
AdmitSource, demogr_urPM.DISCH, demogr_urPM.DISPL, [qry_hosp-
nmePM].HOSP AS DischDest, demogr_urPM.DISSTATUS, status.Status, IIf
([DISPL] Like "KEMH*","KEMH",IIf([DISPL] Is Not Null,IIf([DISPL]
="DIED","Died",IIf([DISPL]="HOME","Home","Elsewhere")),"Unknown")) AS
DischTo, demogr_urPM.DISCHME
FROM ((demogr_urPM LEFT JOIN status ON demogr_urPM.DISSTATUS = status.
[Dis Code]) LEFT JOIN [qry_hosp-nmePM] ON demogr_urPM.DISPL =
[qry_hosp-nmePM].HOSP_ABBREV) LEFT JOIN [qry_hosp-nmePM] AS [qry_hosp-
nmePM_1] ON demogr_urPM.ADMIT_SOURCE = [qry_hosp-nmePM_1].HOSP_ABBREV
WHERE (((demogr_urPM.URN) In (SELECT demogr_urPM.URN
FROM demogr_urPM
WHERE demogr_urPM.ADMIT_SOURCE Like "KEMH*" AND demogr_urPM.ADMN 0
And demogr_urPM.ADMN 10)) AND ((demogr_urPM.ADMN)0 And
(demogr_urPM.ADMN)10))
UNION ALL SELECT demogr_ur.URN, demogr_ur.ADMN, "KEMH" AS Source, Year
([DOA]) AS [Year], [DISCH]-[DOA] AS StayDays, demogr_ur.DOA,
demogr_ur.TOA, demogr_ur.ADMIT_SOURCE, [qry_hosp-nme_1].HOSP AS
AdmitSource, demogr_ur.DISCH, demogr_ur.DISPL, [qry_hosp-nme].HOSP AS
DischDest, demogr_ur.DISSTATUS, status.Status, IIf([DISPL] Like
"KEMH*","KEMH",IIf([DISPL] Is Not Null,IIf([DISPL]="DIED","Died",IIf
([DISPL]="HOME","Home","Elsewhere")),"Unknown")) AS DischTo,
demogr_ur.DISCHME
FROM ((demogr_ur LEFT JOIN status ON demogr_ur.DISSTATUS = status.[Dis
Code]) LEFT JOIN [qry_hosp-nme] ON demogr_ur.DISPL = [qry_hosp-
nme].HOSP_ABBREV) LEFT JOIN [qry_hosp-nme] AS [qry_hosp-nme_1] ON
demogr_ur.ADMIT_SOURCE = [qry_hosp-nme_1].HOSP_ABBREV
WHERE (((demogr_ur.URN) In (SELECT demogr_urPM.URN
FROM demogr_urPM
WHERE demogr_urPM.ADMIT_SOURCE Like "KEMH*" AND demogr_urPM.ADMN 0
And demogr_urPM.ADMN 10)) AND ((demogr_ur.ADMN)0 And (demogr_ur.ADMN)
10))
ORDER BY URN ASC, DOA ASC, DISCH ASC, TOA ASC;

Kind regards,
John McTigue