Struggling with improving efficency of this code



  • QUERY FOR COLUMN INSIDE A VIEW(need for this to work faster)

    CREATE OR REPLACE VIEW "ODS"."VW_YARDI_DLR_PROPERTY_INFORMATION" ("SPCODE", "HMY", "SPNAME", "SCITY", "SSTATE", "SCODE", "SBASECURRENCYCODE", "SFUNCTIONALCURRENCYCODE", "UCODE", "SAPCODE", "SBPCODE", "SBPNUM", "SARCODE", "DTPACQUIRE", "DPPRICE", "SSAMESTORE", "DTPDISPOSE", "DSALESPRICE", "DOWNERSHIPPERCENT", "DTSTABILIZATION", "IPROPCOUNT", "IBLDCOUNT", "IBUILDINGS", "SSTATUS", "SENTITYTYPE", "SOWNERSHIPTYPE", "SPROPERTYTYPE", "OPERATIONALSITECODE", "MASTERSITECODE", "SCAMPUS", "SENTITYPARENT", "SCAM", "SCURRENTYEARSTABILIZED", "SNEXTYEARSTABILIZED", "SDIVISION", "SREPORTINGDIVISION", "SREGION", "SMARKET", "SCOUNTRY", "SSTATE2", "SPROPERTYSOURCECURRENCY", "SOWNEDLEASED", "SELT2000", "SHRISBUSINESSGROUP3000", "SHRISDIVISION4000", "STAXTRSSTATUS", "STAXPARENTCATEGORY", "STAXCATEGORY", "STAXCOUNTRY", "SASSETMANAGER", "SDIRECTOR", "SREGIONALMANAGER", "SREALESTATEMANAGER", "SASSISTANTREM", "SPROPERTYASSISTANT", "BINACTIVE", "DTCREATED", "DTLASTMODIFIED")  AS 
      SELECT            p.sCode AS sPCode,
      P.hmy as hmy
    ,               p.sAddr1 AS sPName  
    ,               p.sCity AS sCity
    ,               p.sState AS sState          
    ,               UPPER(ci.sCode)AS sCode             
    ,               UPPER(dp.sBaseCurrencyCode) AS sBaseCurrencyCode    
    ,               UPPER(dp.sFunctionalCurrencyCode) AS sFunctionalCurrencyCode
    ,               le.uCode AS uCode               
    ,               ab.sAPCode AS sAPCode           
    ,               ab.sBPCode AS sBPCode           
    ,              ab.sBPNum AS     sBPNum          
    ,               ab.sARCode AS sARCode           
    ,               p.sAcquire AS dtPAcquire        
    ,               p.dPPrice AS dPPrice
    ,CASE 
            WHEN p.sDispose ,(SELECT            SUM(  COALESCE(ft8.dPercent, 1.00000000000000000000)
    * COALESCE(ft7.dPercent, 1.00000000000000000000)
    * COALESCE(ft6.dPercent, 1.00000000000000000000)
    * COALESCE(ft5.dPercent, 1.00000000000000000000)
    * COALESCE(ft4.dPercent, 1.00000000000000000000)
    * COALESCE(ft3.dPercent, 1.00000000000000000000)
    * COALESCE(ft2.dPercent, 1.00000000000000000000)
    *ft1.dPercent * 100.00000000)
    FROM ODS.YARDI_Property px
    INNER JOIN ODS.YARDI_Fund_Tran ft1
    ON px.hMy = ft1.hInvestor
    AND ft1.iCategory = 0
    AND ft1.dPercent <> 0
    and SYSDATE BETWEEN ft1.dtStart
    AND nvl(ft1.dtEnd, SYSDATE+1)

                                                    LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft2
                                                    ON              ft1.hInvestment = ft2.hInvestor
                                                    AND             ft1.hInvestment <> P.hMy 
                                                    AND             ft2.iCategory = 0
                                                    AND             ft2.dPercent <> 0
                
                                                    AND             SYSDATE
                                                    BETWEEN         ft2.dtStart
                                                    AND             COALESCE(ft2.dtEnd, SYSDATE)
                                                    
                                                LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft3 
                                                    ON               ft2.hInvestment = ft3.hInvestor
                                                    AND             ft2.hInvestment <> P.hMy
                                                    AND             ft3.iCategory = 0
                                                    AND             ft3.dPercent <> 0
                                                    AND             SYSDATE 
                                                    BETWEEN         ft3.dtStart
                                                    AND             COALESCE(ft3.dtEnd, SYSDATE)
                                                   
                                                    LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft4 
                                                    ON              ft3.hInvestment = ft4.hInvestor
                                                    AND         ft3.hInvestment <> P.hMy
                                                    AND             ft4.iCategory = 0
                                                    AND             ft4.dPercent <> 0
                                                    AND             SYSDATE
                                                    BETWEEN         ft4.dtStart
                                                    AND             COALESCE(ft4.dtEnd, SYSDATE)
                                                  
                                                    LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft5 
                                                    ON                ft4.hInvestment = ft5.hInvestor
                                                    AND             ft4.hInvestment <> P.hMy
                                                    AND             ft5.iCategory = 0
                                                    AND             ft5.dPercent <> 0
                                                    AND             SYSDATE 
                                                    BETWEEN         ft5.dtStart
                                                    AND             COALESCE(ft5.dtEnd, SYSDATE)
                                                   
                                                    LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft6 
                                                    ON              ft5.hInvestment <> P.hMy
                                                    AND             ft5.hInvestment = ft6.hInvestor
                                                    AND             ft6.iCategory = 0
                                                    AND             ft6.dPercent <> 0
                                                    AND             SYSDATE 
                                                    BETWEEN         ft6.dtStart
                                                    AND             COALESCE(ft6.dtEnd,SYSDATE)
                                                  
                                                    LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft7 
                                                    ON              ft6.hInvestment = ft7.hInvestor
                                                    AND             ft6.hInvestment <> P.hMy 
                                                    AND             ft7.iCategory = 0
                                                    AND             ft7.dPercent <> 0
                                                    AND             SYSDATE 
                                                    BETWEEN         ft7.dtStart
                                                    AND             COALESCE(ft7.dtEnd, SYSDATE)
                                                   
                                                    LEFT OUTER JOIN ODS.YARDI_Fund_Tran ft8 
                                                    ON              ft7.hInvestment = ft8.hInvestor
                                                    AND             ft7.hInvestment <> P.hMy 
                                                    AND             ft8.iCategory = 0
                                                    AND             ft8.dPercent <> 0
                                                    AND             SYSDATE 
                                                    BETWEEN         ft8.dtStart
                                                    AND             COALESCE(ft8.dtEnd, SYSDATE)
                                                 
                                                    wHERE   --P.HMY>4643
                                                        trim(trailing ' ' from lower(px.sCode)) =   'rdrtinc'
                                                    AND         
                                                   p.hMy IN
                                                                                (
                                                                                    SELECT FT8.hInvestment  from    dual                        
                                                                                    UNION   ALL
    
                                                                                    SELECT FT7.hInvestment  FROM    dual
    
                                                                                    UNION   ALL
    
                                                                                    SELECT FT6.hInvestment  FROM    dual
    
                                                                                    UNION   ALL
    
                                                                                    SELECT FT5.hInvestment  FROM    dual
    
                                                                                    UNION   ALL
    
                                                                                    SELECT FT4.hInvestment  FROM    dual
    
                                                                                    UNION   ALL
    
                                                                                    SELECT FT3.hInvestment  FROM    dual
    
                                                                                    UNION   ALL
    
                                                                                    SELECT FT2.hInvestment  FROM    dual
    
                                                                                    UNION   ALL
    
                                                                                    SELECT FT1.hInvestment  FROM    dual
                                                                                
                                                ))
                                                 as dOwnershipPercent
    

    , dd.dtStabilization AS dtStabilization
    , dd.iPropCount AS iPropCount
    , dd.iBldCount AS iBldCount
    , (SELECT COUNT(b.hMy)FROM ODS.YARDI_Building b WHERE P.hMy = b.hProp) AS iBuildings
    , atr.SubGroup27 AS sStatus
    , atr.SubGroup23 AS sEntityType
    , trim(trailing ' ' from atr.SubGroup6) AS sOwnershipType
    , trim(trailing ' ' from atr.SubGroup3) AS sPropertyType
    , trim(trailing ' ' from atr.SubGroup75) as OperationalSiteCode
    , trim(trailing ' ' from atr.SubGroup76) as MasterSiteCode
    , atr.SubGroup70 AS sCampus
    , atr.SubGroup34 AS sEntityParent
    , atr.SubGroup35 AS sCAM
    , atr.SubGroup32 AS sCurrentYearStabilized
    , atr.SubGroup33 AS sNextYearStabilized
    , atr.SubGroup11 AS sDivision
    , atr.SubGroup38 AS sReportingDivision
    , trim(trailing ' ' from atr.SubGroup1) AS sRegion
    , trim(trailing ' ' from atr.SubGroup2) AS sMarket
    , atr.SubGroup30 AS sCountry
    , atr.SubGroup31 AS sState2
    , atr.SubGroup18 AS sPropertySourceCurrency
    , atr.SubGroup29 AS sOwnedLeased
    , atr.SubGroup26 AS sELT2000
    , atr.SubGroup15 AS sHRISBusinessGroup3000
    , atr.SubGroup14 AS sHRISDivision4000
    , atr.SubGroup36 AS sTaxTRSStatus
    , atr.SubGroup69 AS sTaxParentCategory
    , atr.SubGroup37 AS sTaxCategory
    , atr.SubGroup39 AS sTaxCountry
    , atr.SubGroup71 AS sAssetManager
    , atr.SubGroup20 AS sDirector
    , atr.SubGroup21 AS sRegionalManager
    , atr.SubGroup22 AS sRealEstateManager
    , atr.SubGroup24 AS sAssistantREM
    , atr.SubGroup25 AS sPropertyAssistant
    , CASE when p.bInActive = - 1
    THEN 'Yes'
    ELSE ''
    END AS bInactive
    , p.dtCreated AS dtCreated
    , p.dtLastModified AS dtLastModified
    FROM ODS.YARDI_Property p
    LEFT OUTER JOIN ODS.YARDI_Country_Info ci
    ON p.hCountry = ci.hMy
    INNER JOIN ODS.VW_YARDI_DLR_PROPERTY dp
    ON p.hMy = dp.hProp
    --left outer join YardiDaily.dbo.LockOut l on p.hMy = l.hProp
    LEFT OUTER JOIN ODS.YARDI_Attributes atr
    ON p.hMy = atr.h_Prop
    LEFT OUTER JOIN ODS.YARDI_OWNER le
    ON p.hLegalEntity = le.hMyPerson
    LEFT OUTER JOIN (
    SELECT px.hMy AS hProp
    , ap.sCode AS sAPCode
    , bp.s_Code AS sBPCode
    , SUBSTR(TRIM(trailing ' ' from bp.s_Acct_Num),-5) AS sBPNum
    , ar.sCode AS sARCode
    /--, br.sCode sBRCode,
    --, right(br.sAcctNum, 5) sBRNum
    /
    FROM ODS.YARDI_Property px
    LEFT OUTER JOIN ODS.YARDI_Acct ap
    ON (ap.hMy,PX.hMy) in (
    SELECT po.h_Value,po.h_Prop
    FROM ODS.YARDI_Prop_Options po
    WHERE 1=1--PX.hMy = po.h_Prop
    AND po.s_Type = 'hPayableCashAcct'
    AND coalesce(po.h_Value,0) <> 0
    )
    LEFT OUTER JOIN ODS.YARDI_Bank_XRef bxp ON pX.hMy = bxp.hPpty
    AND ap.hMy = bxp.h_Acct
    LEFT OUTER JOIN ODS.YARDI_Bank bp
    ON bxp.hBaidx = bp.hMy
    LEFT OUTER JOIN ODS.YARDI_Acct ar
    ON (ar.hMy,px.hmy) in (
    SELECT po.h_Value,po.h_Prop
    FROM ODS.YARDI_Prop_Options po
    WHERE 1=1--X.hMy = po.h_Prop
    AND po.s_Type = 'hReceiptCashAcct'
    AND coalesce(po.h_Value, 0) <> 0
    )
    LEFT OUTER JOIN ODS.YARDI_Bank_XRef bxr
    ON px.hMy = bxr.hPpty
    AND ar.hMy = bxr.h_Acct
    --left outer join YardiDaily.dbo.Bank br on bxr.hBaidx = br.hMy
    WHERE px.iType = 3

                ) ab
    

    ON p.hMy = ab.hProp
    LEFT OUTER JOIN ODS.YARDI_Propbut_DLR_Data dd ON p.hMy = dd.hCode
    AND (dd.dtEffective,p.hmy) in (
    SELECT MAX(x.dtEffective),x.hCode
    FROM ODS.YARDI_Propbut_DLR_Data x
    WHERE 1=1 -- and P.hMy = x.hCode
    --AND x.dtEffective <= DATEADD(SECOND, - 1, DATEADD(dd, 1, GETDATE()))
    AND x.dtEffective <= ((SYSDATE-1)-1/(246060))
    group by x.hcode
    )
    WHERE p.bInactive = 0
    AND p.iType = 3;



  • Step 0: remove the scalar sub query.

    Scalar sub queries are ran for each row in the result set.

    Move it to a CTE (WITH) and join with the rest of the query




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2