I am looking for some tips/tricks to improve performance of a stored procedure with multiple SELECT statements inserting into a table. All objects I am joining on are already indexed.
I believe the reason this stored procedure takes almost an hour to run is because there are multiple SELECT statements using following two views: rvw_FinancialLineItemValues and rvw_FinancialLineItems
Also, each SELECT statement uses specific hard-coded values for AccountNumber, LineItemTypeID, and few other field values coming from two views mentioned above.
Would it improve performance if I create a temporary table, which gets ALL data needed for these SELECT statements at once and then using this temporary table in my join instead?
Are there any other ways to improve performance and manageability?
SELECT
@scenarioid,
@portfolioid,
pa.Id,
pa.ExternalID,
(select value from fn_split(i.AccountNumber,'.') where id = 1),
ac.[Description],
cl.Name,
NullIf((select value from fn_split(i.AccountNumber,'.') where id = 2),''),
NullIf((select value from fn_split(i.AccountNumber,'.') where id = 3),''),
ty.Name,
v.[Date],
cast(SUM(v.Amount) as decimal(13,2)),
GETDATE()
FROM rvw_FinancialLineItems i
INNER JOIN rvw_Scenarios sc
ON i.ScenarioId = sc.Id
AND sc.Id = @scenarioid
AND sc.PortfolioId = @portfolioid
INNER JOIN #pa AS pa
ON i.PropertyAssetID = pa.Id
INNER JOIN rvw_FinancialLineItemValues v
ON i.ScenarioId = v.ScenarioId
AND i.PropertyAssetID = v.PropertyAssetID
AND i.Id = v.FinancialLineItemId
AND ((i.BusinessEntityTypeId = 11
AND i.LineItemTypeId = 3002)
OR (i.LineItemTypeId IN (2005, 2010, 2003, 2125, 2209, 5012, 6001)
AND i.ModeledEntityKey = 1))
AND i.AccountNumber not in ('401ZZ','403ZZ')
AND i.AccountNumber not in ('401XX')
AND i.AccountNumber not in ('40310','41110','42010','41510','40190','40110') -- exclude lease-level revenues selected below
AND v.[Date] BETWEEN @fromdate AND
CASE
WHEN pa.AnalysisEnd < @todate THEN pa.AnalysisEnd
ELSE @todate
END
AND v.ResultSet IN (0, 4)
INNER JOIN rvw_Portfolios po
ON po.Id = @portfolioid
INNER JOIN Accounts ac
ON po.ChartOfAccountId = ac.ChartOfAccountId
AND i.AccountNumber = ac.AccountNumber
AND ac.HasSubAccounts = 0
INNER JOIN fn_LookupClassTypes() cl
ON ac.ClassTypeId = cl.Id
INNER JOIN LineItemTypes ty
ON ac.LineItemTypeId = ty.Id
LEFT JOIN OtherRevenues r
ON i.PropertyAssetID = r.PropertyAssetID
AND i.AccountNumber = r.AccountID
AND v.[Date] BETWEEN r.[Begin] AND r.[End]
WHERE (r.IsMemo IS NULL
OR r.IsMemo = 0)
GROUP BY pa.AnalysisBegin
,pa.Id
,pa.ExternalID
,i.AccountNumber
,ac.[Description]
,cl.Name
,ty.Name
,v.[Date]
HAVING SUM(v.amount) <> 0
First thing, which
fn_split()
UDF are you using? If you are not using the table-Valued inline UDF, then this is notoriously slow.Second, is the UDF
fn_LookupClassTypes()
an inline table valued UDF? If not, convert it to an inline Table-Valued UDF.Last, your SQL query had some redundancies. Try this and see what it does.