title: Optimizing Slow T-SQL Stored Procedure
body: <p>I am working on creating a stored procedure for a table in my database; however, it runs extremely slow due to a large number of joins that must be performed. I am trying to think of a way to optimize the query so that I do not need to perform so many left joins, but I am having trouble finding a way to do this. My code looks like the following:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[STS]
  AS
  DECLARE @t0 table(Id nvarchar(7), Date1 date, TIV float, [1mo] float, RAN nvarchar(50), SAN nvarchar(50))
  INSERT INTO @t0(Id, Date1, TIV, [1mo], RAN, SAN)
  SELECT Id, Date1, TIV, TMR, RAN, SAN
  FROM dbo.History
  WHERE (Date1 IS NOT NULL) AND (Valid IS NULL OR Valid &lt;&gt; 0) AND (include &lt;&gt; 0)
  
  DECLARE @t1 table(Id nvarchar(7), Date1 date, TIV float, [3mo] float, RAN nvarchar(50), SAN nvarchar(50))
  INSERT INTO @t1(Id, Date1, TIV, [3mo], RAN, SAN) 
  SELECT * FROM dbo.Series(3)
  
  DECLARE @t2 table(Id nvarchar(7), Date1 date, TIV float, [6mo] float, RAN nvarchar(50), SAN nvarchar(50))
  INSERT INTO @t2(Id, Date1, TIV, [6mo], RAN, SAN) 
  SELECT * FROM dbo.Series(6)
  
  DECLARE @t3 table(Id nvarchar(7), Date1 date, TIV float, [9mo] float, RAN nvarchar(50), SAN nvarchar(50))
  INSERT INTO @t3(Id, Date1, TIV, [9mo], RAN, SAN) 
  SELECT * FROM dbo.Series(9)
  
  
   SELECT t0.*, Join2.[3mo], Join2.[6mo], Join2.[9mo]
   FROM @t0 as t0
   LEFT OUTER JOIN
       (SELECT t1.*, Join1.[6mo], Join1.[9mo]
       FROM @t1 as t1
       LEFT OUTER JOIN
           (SELECT t2.*, t3.[9mo]
           FROM @t2 as t2
           LEFT OUTER JOIN @t3 as t3 
           ON t2.Id = t3.Id AND t2.Date1 = t3.Date1 AND t2.RAN = t3.RAN AND 
               t2.SAN = t3.SAN) as Join1
       ON t1.Id = Join1.Id AND t1.Date1 = Join1.Date1 AND t1.RAN = Join1.RAN AND
               t1.SAN = Join1.SAN) as Join2
   ON t0.Id = Join2.Id AND t0.Date1 = Join2.Date1 AND t0.RAN = Join2.RAN AND
               t0.SAN = Join2.SAN
  </code></pre>
  
  <p>Is there a simple way to optimize this slow query, or do I need to think of a new way to do this altogether?</p>