title: Improve performance of a sql query
body: <p>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. </p>
  
  <p>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</p>
  
  <p>Also, each SELECT statement uses specific hard-coded values for AccountNumber, LineItemTypeID, and few other field values coming from two views mentioned above.</p>
  
  <p>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?</p>
  
  <p>Are there any other ways to improve performance and manageability?</p>
  
  <pre><code>        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 &lt; @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) &lt;&gt; 0
  </code></pre>