ridiculously time-consuming subqueries
- From: William Threlfall <William.Threlfall@xxxxxxxxxxxxxxxxxxxxxxxx>
- To: "Oracle-L@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
- Date: Wed, 12 Mar 2014 11:51:57 -0600
Hi experts,
I have a query in which I am trying to get the CMV and EBV results for the
patient's last transplant.
I put in the following subquery code:
(SELECT RECIPIENT_CMV_IGG
FROM PAT_TRANSPLANT PT, SDF_SEROLOGY_VERIFICATION_TO SV,
DOCUMENTS DOCS, TX_DOCUMENTS TXD
WHERE PT.PAT_ID = PD.PAT_ID
AND PT.PAT_ID = DOCS.PAT_ID
AND PT.PAT_ID = LTV.PAT_ID
AND PT.TRANSPLANT_ID = TXD.TRANSPLANT_ID
AND PT.DATE_OF_TRANS = LTV.LAST_TRANS_DATE
AND DOCS.DOC_ID = TXD.DOC_ID
AND DOCS.DOC_ID = SV.DOC_ID
AND DOCS.DOC_KIND_ID = 10108
) AS CMV,
(SELECT RECIPIENT_EBV
FROM PAT_TRANSPLANT PT, SDF_SEROLOGY_VERIFICATION_TO SV,
DOCUMENTS DOCS, TX_DOCUMENTS TXD
WHERE PT.PAT_ID = PD.PAT_ID
AND PT.PAT_ID = DOCS.PAT_ID
AND PT.PAT_ID = LTV.PAT_ID
AND PT.TRANSPLANT_ID = TXD.TRANSPLANT_ID
AND PT.DATE_OF_TRANS = LTV.LAST_TRANS_DATE
AND DOCS.DOC_ID = TXD.DOC_ID
AND DOCS.DOC_ID = SV.DOC_ID
AND DOCS.DOC_KIND_ID = 10108
) AS EBV
In the above code, PD = PATIENT_DEMO and LTV = LAST_TRANSPLANT_VIEW, which are
in the FROM clause in the main query.
When I comment out these subqueries, the rest of the main query executes in
less than 5 minutes, but with these subqueries in I had to stop the query
because it was running for over an hour and still wasn't finished yet.
Can anyone see any obvious explanation for why these subqueries are taking so
long to execute?
Can anyone suggest a better way to get the CMV and EBV results for the last
transplant?
I only want one CMV and one EBV result (i.e. one value for each, in one row)
returned per patient. I am not certain if there are multiple 10108 documents
attached to the last transplant for any patients - that is possible. In that
case I want the result to be "POSITIVE" if any of them are POSITIVE, otherwise
"NEGATIVE".
Thanks, - Bill.
_______________________________________
Willliam J. (Bill) Threlfall, MSc
Clinical Informatics Coordinator - OTTR
Transplant Services, University of Alberta Hospital
Alberta Health Services
Aberhart Centre, Room 9221
8440 112 Street
Edmonton, AB T6G 2B7
Telephone: 780-407-6175 FAX: 780-407-8981
Alberta Health Services
www.albertahealhservices.ca
This message, and any documents attached hereto, is intended only for the
addressee and may contain privileged or confidential information. any
unauthorized disclosure is strictly prohibited. If you have received this
message in error, please notify us immediately so that we may correct our
internal records. Please then delete the original message. Thank you.
________________________________
This message and any attached documents are only for the use of the intended
recipient(s), are confidential and may contain privileged information. Any
unauthorized review, use, retransmission, or other disclosure is strictly
prohibited. If you have received this message in error, please notify the
sender immediately, and then delete the original message. Thank you.
Other related posts:
