Re: ridiculously time-consuming subqueries

    Generally speaking, avoid subqueries in the "select list" as you have here. You are hitting the same tables twice, with the same conditions. Try to have joins, but subqueries in the "select list". Besides, I always check views in complex queries. You cannot imagine how much you speed up queries sometimes by just pasting the view definition in the FROM clause, and removing from it everything that isn't necessary for your query.

HTH,

-- 
Stéphane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Author, SQL Success
<http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>,
The Art of SQL
<http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>,
Refactoring SQL Applications
<http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/>



On 03/12/2014 06:51 PM, William Threlfall wrote:


> *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 patien**t. I am not certain if there
> are multiple 10108 document**s**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.


--
http://www.freelists.org/webpage/oracle-l