If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
UNION query taking 5 minutes for approx 2000 records - any tune up tips?
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 |
Thread Tools | |
Display Modes | |
|
|