This discussion is archived
9 Replies Latest reply: Jun 19, 2013 3:04 AM by Hoek RSS

modify the Query

982895 Newbie
Currently Being Moderated

Hi, Can the left outer join be replaced with Inner join? And can the Query be modified to improve performance. IS NULL conditions might take longer time..so can it be replaced in any way.. SELECT OD.OTHERSERVICEDETAILID,       OD.OTHERSERVICESREQUESTNO,       OD.REQUESTINGPERSON,       OD.SERVICEITEMID,       F_GETEHISLOVMEANING(OD.PRIORITYID) PRIORITY,       F_GETSTATUSMEANING(OD.REQUESTSTATUS,                           'OTHERSERVICEDETAILS',                           'REQUESTSTATUS') REQUESTSTATUS,       OD.STATUS,       (CASE         WHEN OM.Patientserviceid = 1 THEN           NULL         WHEN OM.CREATEDBY = 0 THEN           NULL         ELSE           OD.CREATEDBY       END) CREATEDBY,       OD.CREATEDDATE REQUESTDATE,       OD.UPDATEDBY,       OD.UPDATEDDATE,       OD.LOCATIONID,       OD.COMMENTS,       OD.PRIORITYID,       OD.DEPTID,       DM.DEPTNAME DEPTNAME,       TO_CHAR(OD.STARTDATETIME, 'mm/dd/yyyy HH24:MI:ss') STARTDATETIME,       OM.IPNUMBER IPNUMBER,       RM.ROOMNAME,       TO_CHAR(OM.IPNUMBER || '.' || OD.SERVICEITEMID) ACCESSIONNO,       OD.APPOINTMENTID,       OM.UHID,       OD.QUANTITY,       OD.CONSUMPTIONQTY   FROM OTHERSERVICEDETAILS OD INNER JOIN OTHERSERVICESMASTER OM     ON OM.OTHERSERVICESREQUESTNO = OD.OTHERSERVICESREQUESTNO   LEFT OUTER JOIN EHIS.ROOMMASTER RM     ON RM.ROOMID = OD.REQUIREDAT   AND RM.LOCATIONID = IV_LOCATIONID   LEFT OUTER JOIN DEPTMASTER DM     ON DM.DEPTID = OD.DEPTID   AND DM.STATUS = 1   AND DM.LOCATIONID = IV_LOCATIONID WHERE (IN_REQUESTSTATUS IS NULL OR OD.REQUESTSTATUS = IN_REQUESTSTATUS)   AND OD.STATUS = 1   AND (IN_REQUESTNO IS NULL OR OD.OTHERSERVICESREQUESTNO = IN_REQUESTNO) * /   AND (V_REQUESTNO IS NULL OR OD.OTHERSERVICESREQUESTNO = V_REQUESTNO)   AND (VD_FROMDATE IS NULL OR OD.CREATEDDATE = VD_FROMDATE)   AND (VD_TODATE IS NULL OR OD.CREATEDDATE = VD_TODATE)   AND (IV_PATIENTTYPE IS NULL OR OM.PATIENTSERVICEID = IV_PATIENTTYPE)   AND (IV_IPNUMBER IS NULL OR OM.IPNUMBER = IV_IPNUMBER)   AND OD.STATUS = 1   AND OD.LOCATIONID = IV_LOCATIONID   AND (IV_UHID IS NULL OR OM.UHID = IV_UHID)   AND (IN_DEPTID IS NULL OR OD.DEPTID = IN_DEPTID)   AND (IV_OTHERSERVICEDETAILID IS NULL OR       OD.OTHERSERVICEDETAILID = IV_OTHERSERVICEDETAILID) ORDER BY OD.OTHERSERVICESREQUESTNO DESC; Thanks in advance..

  • 1. Re: modify the Query
    Hoek Guru
    Currently Being Moderated

    Please read the steps listed here:

    Re: 2. How do I ask a question on the forums?
    HOW TO: Post a SQL statement tuning request - template posting

    and make your code readable and add the details (database version, execution plan etc. etc.) we need to know in order to be of help.

  • 2. Re: modify the Query
    BluShadow Guru Moderator
    Currently Being Moderated

    Ok, I've formatted for you (in future do this yourself if you want help)...

     

    SELECT OD.OTHERSERVICEDETAILID

          ,OD.OTHERSERVICESREQUESTNO

          ,OD.REQUESTINGPERSON

          ,OD.SERVICEITEMID

          ,F_GETEHISLOVMEANING(OD.PRIORITYID) PRIORITY

          ,F_GETSTATUSMEANING(OD.REQUESTSTATUS, 'OTHERSERVICEDETAILS', 'REQUESTSTATUS') REQUESTSTATUS

          ,OD.STATUS

          ,(CASE WHEN OM.Patientserviceid = 1 THEN NULL

                 WHEN OM.CREATEDBY = 0 THEN NULL

            ELSE OD.CREATEDBY

            END) CREATEDBY

          ,OD.CREATEDDATE REQUESTDATE

          ,OD.UPDATEDBY

          ,OD.UPDATEDDATE

          ,OD.LOCATIONID

          ,OD.COMMENTS

          ,OD.PRIORITYID

          ,OD.DEPTID

          ,DM.DEPTNAME DEPTNAME

          ,TO_CHAR(OD.STARTDATETIME, 'mm/dd/yyyy HH24:MI:ss') STARTDATETIME

          ,OM.IPNUMBER IPNUMBER

          ,RM.ROOMNAME

          ,TO_CHAR(OM.IPNUMBER || '.' || OD.SERVICEITEMID) ACCESSIONNO

          ,OD.APPOINTMENTID

          ,OM.UHID

          ,OD.QUANTITY

          ,OD.CONSUMPTIONQTY

    FROM   OTHERSERVICEDETAILS OD

           INNER JOIN OTHERSERVICESMASTER OM ON OM.OTHERSERVICESREQUESTNO = OD.OTHERSERVICESREQUESTNO

           LEFT OUTER JOIN EHIS.ROOMMASTER RM ON RM.ROOMID = OD.REQUIREDAT

                                             AND RM.LOCATIONID = IV_LOCATIONID

           LEFT OUTER JOIN DEPTMASTER DM ON DM.DEPTID = OD.DEPTID

                                        AND DM.STATUS = 1

                                        AND DM.LOCATIONID = IV_LOCATIONID

    WHERE (   IN_REQUESTSTATUS IS NULL

           OR OD.REQUESTSTATUS = IN_REQUESTSTATUS

          )

    AND    OD.STATUS = 1

    AND   (   IN_REQUESTNO IS NULL

           OR OD.OTHERSERVICESREQUESTNO = IN_REQUESTNO

          ) * /

    AND   (   V_REQUESTNO IS NULL

           OR OD.OTHERSERVICESREQUESTNO = V_REQUESTNO

          )

    AND   (   VD_FROMDATE IS NULL

           OR OD.CREATEDDATE = VD_FROMDATE

          )

    AND   (   VD_TODATE IS NULL

           OR OD.CREATEDDATE = VD_TODATE

          )

    AND   (   IV_PATIENTTYPE IS NULL

           OR OM.PATIENTSERVICEID = IV_PATIENTTYPE

          )

    AND   (   IV_IPNUMBER IS NULL

           OR OM.IPNUMBER = IV_IPNUMBER

          )

    AND   OD.STATUS = 1

    AND   OD.LOCATIONID = IV_LOCATIONID

    AND   (   IV_UHID IS NULL

           OR OM.UHID = IV_UHID

          )

    AND   (   IN_DEPTID IS NULL

           OR OD.DEPTID = IN_DEPTID

          )

    AND   (   IV_OTHERSERVICEDETAILID IS NULL

           OR OD.OTHERSERVICEDETAILID = IV_OTHERSERVICEDETAILID

          )

    ORDER BY OD.OTHERSERVICESREQUESTNO DESC;

     

    a) getting rid of the left outer join... that depends on your data and whether the left outer join is appropriate or not - why do you want to get rid of it?

    b) performance could likely be seriously impacted by the calls to the F_GETEHISLOVMEANING and F_GETSTATUSMEANING functions.  These are causing context switching for each row retrieved.  Try and put the functionality of the functions directly in SQL so you don't have to all PL/SQL at all.

    c) not sure what the * / is in your code... a copy paste error?  something commented out that you've not posted full details of?

  • 3. Re: modify the Query
    982895 Newbie
    Currently Being Moderated

    Hi Blushadow, very much thankful for helping me out for formatting the code.. I was trying but failed anyways will surely try next time.. Ok coming to the points.. 1)*/ is typo mistake..u can ignore that.. 2) Wanted to get rid of left outer join because inner join is better in performance than outer join (if im not wrong).. 3) yes I tries to get rid of the functions but missing the functionaly..anyways I will takecare of this part.. 4)can this type of code (IN_REQUESTSTATUS IS NULL OR OD.REQUESTSTATUS = IN_REQUESTSTATUS) be rewritten as OD.REQUESTSTATUS = IN_REQUESTSTATUS because read in some post that IS NULL search takes lot of time

  • 4. Re: modify the Query
    Etbin Guru
    Currently Being Moderated

    Maybe - just formatted and minor changes

     

    select od.otherservicedetailid,

           od.otherservicesrequestno,

           od.requestingperson,

           od.serviceitemid,

           f_getehislovmeaning(od.priorityid) priority,

           f_getstatusmeaning(od.requeststatus,'OTHERSERVICEDETAILS','REQUESTSTATUS') requeststatus,

           od.status,

           case when om.patientserviceid = 1

                  or om.createdby = 0

                then null

                else od.createdby

           end createdby,

           od.createddate requestdate,

           od.updatedby,

           od.updateddate,

           od.locationid,

           od.comments,

           od.priorityid,

           od.deptid,

           dm.deptname,

           to_char(od.startdatetime,'mm/dd/yyyy hh24:mi:ss') startdatetime,

           om.ipnumber,

           rm.roomname,

           to_char(om.ipnumber) || '.' || to_char(od.serviceitemid) accessionno,

           od.appointmentid,

           om.uhid,

           od.quantity,

           od.consumptionqty

      from otherservicedetails od

           inner join

           otherservicesmaster om

        on om.otherservicesrequestno = od.otherservicesrequestno

           left outer join

           ehis.roommaster rm

        on rm.roomid = od.requiredat

       and rm.locationid = iv_locationid

           left outer join

           deptmaster dm

        on dm.deptid = od.deptid

       and dm.status = 1

       and dm.locationid = iv_locationid

    where od.locationid = iv_locationid

       and od.status = 1

       and lnnvl(od.requeststatus != in_requeststatus)

       and lnnvl(od.otherservicesrequestno != in_requestno)

       and lnnvl(od.otherservicesrequestno != v_requestno)

       and lnnvl(od.createddate != vd_fromdate)

       and lnnvl(od.createddate != vd_todate)

       and lnnvl(om.patientserviceid != iv_patienttype)

       and lnnvl(om.ipnumber != iv_ipnumber)

       and lnnvl(om.uhid != iv_uhid)

       and lnnvl(od.deptid != in_deptid)

       and lnnvl(od.otherservicedetailid != iv_otherservicedetailid)

    order by od.otherservicesrequestno desc

     

    Regards

     

    Etbin

  • 5. Re: modify the Query
    Chris Hunt Journeyer
    Currently Being Moderated

    You see that big button with the arrow on it near the right-hand side of your keyboard? It's called the carriage return button. It's really useful in making your posts more readable.

     

    See?

     

    Anyhow,

    Wanted to get rid of left outer join because inner join is better in performance than outer join (if im not wrong)..

    It can be quicker, but we can't tell you whether the outer join can be replaced with inner joins without knowing about your data.

     

    If every row in Otherservicedetails has corresponding rows in Roommaster and Deptmaster ( the latter with status = 1), then you should be able to use inner joins instead because the outer join isn't doing anything.

     

    can this type of code (IN_REQUESTSTATUS IS NULL OR OD.REQUESTSTATUS = IN_REQUESTSTATUS) be rewritten as OD.REQUESTSTATUS = IN_REQUESTSTATUS

    Yes, but it won't have the same functionality. If IN_REQUESTSTATUS always has a value in it, then rewrite your query as above. If it can have NULLs in it, which should be ignored, then you have to do something about that. However, I'd suggest rewriting each such clause like this:

    OD.REQUESTSTATUS = NVL(IN_REQUESTSTATUS,OD.REQUESTSTATUS)

    I think this has a better chance of using indexes - but you'd have experiment to find out for sure. Note that this will only work properly if OD.REQUESTSTATUS is itself a not-null column.

     

    read in some post that IS NULL search takes lot of time

    There's nothing particularly time-consuming about IS NULL, except that (I believe) it tends not to use indexes on the column concerned - which can certainly slow things up.

  • 6. Re: modify the Query
    BluShadow Guru Moderator
    Currently Being Moderated

    ChrisHunt wrote:

     

    read in some post that IS NULL search takes lot of time

    There's nothing particularly time-consuming about IS NULL, except that (I believe) it tends not to use indexes on the column concerned - which can certainly slow things up.

     

    Unless there is bitmap indexes, which can also index NULL values.

  • 7. Re: modify the Query
    982895 Newbie
    Currently Being Moderated

    Thanku very much for the useful suggestions chrishunt. How can we replace (VD_FROMDATE IS NULL OR OD.CREATEDDATE >= VD_FROMDATE)? using NVL..

  • 8. Re: modify the Query
    Hoek Guru
    Currently Being Moderated

    Try:

    OD.CREATEDDATE >= NVL(VD_FROMDATE, OD.CREATEDDATE)

  • 9. Re: modify the Query
    982895 Newbie
    Currently Being Moderated

    thanku hoek

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points