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) abON 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
