9 Replies Latest reply: Jun 19, 2013 5:04 AM by Hoek RSS

    modify the Query

    982895

      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

          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

            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

              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

                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

                  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

                    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

                      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

                        Try:

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

                        • 9. Re: modify the Query
                          982895

                          thanku hoek